Setting Index Partitions as Unusable and Then Rebuilding
In a data warehouse environment, when loading large volumes of data, the speed of bulk DML operations can be slowed tremendously by the presence of indexes. One of the key advantages of the partitioned index is the ability to set portions of the index UNUSABLE prior to a bulk data load, and then simply rebuild on the portion of the index after the load based on the partitions impacted.
At its most basic, it is fairly simple to mark an index unusable and then rebuild an index. For example,
SQL> alter table employees_parttest
2 modify partition pmax
3 unusable local indexes;
Then, after the bulk load operation, you can issue the following command to rebuild the indexes for the given ...
Get Expert Indexing in Oracle Database 11g: Maximum Performance for Your Database now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
