Rebuilding Indexes the Easy Way

Oracle has made index rebuilding easier through the introduction of the DBMS_INDEX_UTL PL/SQL package back in 10g.

In lieu of creating hundreds of seperate rebuild DDL statements, you can now create single calls to this package's methods in order to accomplish the same task(s).

This example demonstrates how to rebuild all indexes - including those with partitions - for the SH.SALES table - by constructing a single call to the aforementioned package.

Here are the indexes in question for my test system:

SELECT 
     TRIM(di.owner) "OWNER", TRIM (di.index_name) "INDEX",
     TRIM(MAX (TO_CHAR (dip.last_analyzed, 'MON-DD-YYYY HH24:MI:SS'))) "LAST ANALYZED",
     COUNT(dip.partition_name) "PART COUNT",
     ROUND(SUM (ds.BYTES) / 1024) "KB"
     FROM  dba_indexes di, 
           dba_ind_partitions dip, 
           dba_segments ds
     WHERE di.index_name = ds.segment_name
     AND di.owner = ds.owner
     AND dip.partition_name = ds.partition_name
     AND di.table_owner = 'SH'
     AND di.table_name = 'SALES'
     AND di.index_name = dip.index_name
     AND di.owner = dip.index_owner
     AND di.owner = dip.index_owner
GROUP BY di.owner, di.index_name;

OWNER                          INDEX                          LAST ANALYZED              PART COUNT         KB
------------------------------ ------------------------------ -------------------------- ---------- ----------
SH                             SALES_TIME_BIX                 JUN-30-2008 16:14:27               28       9216
SH                             SALES_CHANNEL_BIX              JUN-30-2008 16:14:18               28       4288
SH                             SALES_PROD_BIX                 JUN-30-2008 16:14:24               28       5056
SH                             SALES_CUST_BIX                 JUN-30-2008 16:14:23               28      46848
SH                             SALES_PROMO_BIX                JUN-30-2008 16:14:26               28       2944

5 rows selected.

I will rebuild all these indexes via the following call:

EXEC dbms_index_utl.build_table_indexes (list=>'SH.SALES',just_unusable=>FALSE, locality=>'ALL',concurrent=>TRUE, cont_after_err=>TRUE, max_slaves=>8);
PL/SQL procedure successfully completed.

Afterwards, the same query shows that the overall footprint of the indexes in question has been reduced. Additionally, the segments have been analyzed as part of the rebuild.

OWNER                          INDEX                          LAST ANALYZED              PART COUNT         KB
------------------------------ ------------------------------ -------------------------- ---------- ----------
SH                             SALES_TIME_BIX                 JUN-30-2008 17:03:47               28       7104
SH                             SALES_CHANNEL_BIX              JUN-30-2008 17:03:42               28       4288
SH                             SALES_PROD_BIX                 JUN-30-2008 17:03:44               28       3968
SH                             SALES_CUST_BIX                 JUN-30-2008 17:03:44               28      37632
SH                             SALES_PROMO_BIX                JUN-30-2008 17:03:46               28       3008

5 rows selected.

Labels: Maintenance