Hi
My take on table Partitioning is, it facilitates large chunks of data into/out of a main table, limiting table's downtime, due to locking. Other than that, its of no specific use. It may also facilitate occasional DBA maintenance activities like compression etc. But, it is of little use in terms of day to day DML operations, especially gauged against the influence offered by Indexing.
For example, if a table is partitioned on date column, the partition feature shines when the entire particular dated rows are to be deleted. However, if within a given date, only certain specific rows are to be deleted, then, its actually not Partition policy, but the indexing mechanism is what shines.
(1) In cases of selective DELETEs, the performance is same both on a Partitioned table with Index and an Unpartitioned table with Index. Is this understanding correct?
(2) Could you please share your thoughts on how do other variants of DML operations (SELECT, UPDATE, INSERT) get influenced in specific scenarios by partition feature?
many thanks