Databases Reference
In-Depth Information
INDEX_NAME PART_NAME STATUS
--------------------- ------------- --------
EMP_GLOBAL_HASH_IDX P1 USABLE
EMP_GLOBAL_HASH_IDX P2 USABLE
SQL>
Notice that if you add a partition to the table, the indexes become unusable if you don't specify the
update global indexes
clause in your add partition statement
.
SQL> alter table emp_hpart add partition q7;
Table altered.
SQL> select substr(index_name,1,20) index_name, substr(partition_name,1,20)
2 part_name , status
3 from dba_ind_partitions
4* where index_name= 'EMP_GLOBAL_HASH_IDX' order by partition_name
SQL> /
INDEX_NAME PART_NAME STATUS
-------------------- ----------- -----------
EMP_GLOBAL_HASH_IDX P1 UNUSABLE
EMP_GLOBAL_HASH_IDX P2 UNUSABLE
SQL>
Rebuilding Indexes Frequently
Oracle itself has changed its stand on the advisability of rebuilding indexes. Until recently, Oracle
Support used to offer a standard script to identify candidates for an index rebuild. The script included an
analyze index ...validate structure
statement to populate the
INDEX_STATS
view. Once the indexes
are analyzed, you use two simple criteria to identify the indexes that could potentially benefit from a
rebuild. The summary of Oracle's advice was to rebuild any index that met the following two criteria:
•
e i
x
h is m
e
n 4 l l .
•
The deleted index entries are at least 20% of the total current index entries.
Note
The database implements an index update internally by performing a
delete
first and then an
insert
.
Many practitioners still rely on these outmoded and wrong rebuild criteria to determine whether
they should rebuild their indexes. The latest Oracle documentation continues to recommend regular
rebuilding of indexes. It urges you to "develop a history of average efficiency of index usage" by
frequently running the
validate index …analyze structure
command and rebuilding indexes based on
