This content has been marked as final. Show 5 replies
one of the main principles behind partitioning is that you can treat a partitioned table exactly the same as a non-partitioned table from a DML point of view. The user and the application doesn't have to be aware that they are dealing with a partitioned object. So the delete command that you use against a non-partitioned table will work against a partitioned table.
Perhaps a visit to the documentation would help.
874823 wrote:This approach is wrong - as Andre pointed, this is not how partition tables should be used.
delete from table1 partition (P_2008_1212)
Oracle supports different structures for data and indexes. A table can be a hash table or index organised table. It can have B+tree index. It can have bitmap indexes. It can be partitioned. Etc.
How the table implements its structure is a physical design consideration.
Application code should only deal with the logical data structure. How that data structure is physically implemented has no bearing on application. Does your application need to know what the indexes are and the names of the indexes,in order to use a table? Obviously not. So why then does your application need to know that the table is partitioned?
When your application code starts referring directly to physical partitions, it needs to know HOW the table is partitioned. It needs to know WHAT partitions to use. It needs to know the names of the partitions. Etc.
And why? All this means is increased complexity in application code as this code now needs to know and understand the physical data structure. This app code is now more complex, has more moving parts, will have more bugs, and will be more complex to maintain.
Oracle can take an app SQL and it can determine (based on the predicates of the SQL), which partitions to use and not use for executing that SQL. All done totally transparently. The app does not need to know that the table is even partitioned.
This is a crucial concept to understand and get right.