ManagingIndexes文件材料教学稿件.pptVIP

Coalescing Indexes When you encounter index fragmentation, you can rebuild or coalesce the index. Before you perform either task, you should consider the cost and benefits of each option and choose the one that works best for your situation. Coalesce on an index is a block rebuild that is done online. In situations where you have B-tree index leaf blocks that can be freed up for reuse, you can merge those leaf blocks using the following SQL statement: ALTER INDEX hr.employees_idx COALESCE; The figure above shows the effect of ALTER INDEX … COALESCE on the index hr.employees_idx. Before performing the COALESCE operation, the first two leaf blocks are 50% full. This means the index is fragmented and can be coalesced to completely filling the first block, reducing fragmentation. Analyzing an Index Analyze the index to perform the following: Check all the index blocks for block corruption. Note that this command does not verify whether index entries correspond to data in the table. Populate the INDEX_STATS view with information about the index. Syntax ANALYZE INDEX [ schema.]index VALIDATE STRUCTURE After running this command, query INDEX_STATS to obtain information about the index as shown in the following example: Checking Indexes and Their Validity (continued) SELECT blocks, pct_used, distinct_keys lf_rows, del_lf_rows FROM index_stats; BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS 25 11 14 0 1 row selected. Reorganize the index if it has a high proportion of deleted rows. For example: when the ratio of DEL_LF_ROWS to LF_ROWS exceeds 30%. When Should Indexes Be Dropped? Indexes may need to be dropped in the following scenarios: An index that is no longer needed by applications can be removed. An index may be dropped prior to performing bulk loads. Dropping an index prior to large data loads and re-creating them after the load: Improves performance of the load Uses index space more efficiently Indexes that are used only periodically do not need to be