Re: vacuum vs vacuum full

[Ravi Krishna <srkrishna@xxxxxxxxx>]

Experience shows that global index in Oracle lead to problems when dropping a partition. rebuilding an index, or other such nice administrative stuff, often leading to unnecessarily long downtimes.

I think Oracle fixed it later by allowing asynchronous update of global index after the detachment of partition.

ALTER TABLE TABLE_NAME DROP PARTITION PARTITION_NAME UPDATE INDEXES;

will immediately start maintenance of global index by cleaning it up asynchronously, while the index is marked valid and can be used by the applications.

DB2 also has the same feature for a long time and it works fine.

I am sure there are genuine use cases of global indexes.