[bbk2863] 第54集 - Chapter 13-Using Oracle Blokcs Efficeintly[03]

Index Reorganization

• Indexes on volatile tables are a performance problem.
• Only entirely empty index blocks go to the free list.
• If a block contains only one entry,it must be maintained.
• You may need to rebuild indexes.

Monitoring Indexes Space

• To collect usage statistics regarding an index:(搜集索引的相关数据)

SQL>EXECUTE dbms_stats.gather_index_stats('OE','CUSTOMER_PK');

 

• To view statistics collected:

SQL> select name,(del_lf_rows_len/lf_rows_len) * 100 as wastage from index_stats;

 

• Rebuild indexes with wastage greater than 20%:(推到重建)

SQL>ALTER INDEX oe.customers_pk REBUILD;

 

• To coalesce indexes(alternative to REBUILD):(小修小补)

SQL>ALTER INDEX oe.customers_pk COALESCE;

 

何时需要重新构建索引？

假如有一列主键索引，自动增长的,100多万条数据，其中20多万被删除了，索引也就无效了，这个时候可以判定这被删除的20多万条索引，以后将永远不会被使用了，此时就可以进行重新rebuild;如果某些情况下的索引，即使记录被删除了，索引部分任然可以重复利用，则就不需要再重新构建.

Index Coalesce

Guidelines

rebuild index 加速方法

1、通过并行方式处理，来增加几个进程，加快rebuild index 的速度.

2、nologing方式，增加rebuild index 的速度.

Monitoring Index usage

• Gathering statistics using an Oracle supplied package:

关于监控索引的使用情况，我们主要使用到的方法:

 

SQL>EXECUTE dbms_stats.gather_index_stats('HR','LOC_COUNTRY_IX');

 

SQL>ANALYZE TABLE HR.Employees COMPUTE STATISTICS;

 

• Gathering statistics at index creation:
  • 在创建Index时，添加COMPUTE STATISTICS已经基本过时了，现在的Oracle版本基本上都是默认支持；

SQL>CREATE INDEX hr.loc_country_ix
............................
COMPUTE STATISTICS;

 

• Gathering statistics when rebuilding an index:
  • 在创建Index时，添加COMPUTE STATISTICS已经基本过时了，现在的Oracle版本基本上都是默认支持；
    

SQL>ALTER INDEX hr.loc_country_ix REBUILD COMPUTE STATISTICS;

 

Identifying Unused Indexes

• To start monitoring the usage of an index:

SQL>ALTER INDEX hr.emp_name_ix MONITORING USAGE;

 

• To query the usage of the index:

SQL>SELECT index_name,used FROM v$object_usage;

 

• To stop monitoring the usage of an index:

SQL>ALTER INDEX hr.emp_name_ix NOMONITORING USAGE;

 

Summary

In this lesson,you should have learned to do the following:

• Use automatic segment space management
• Use manual segment space management
  • -Manage extents and Oracle blocks
  • -Ensuring effective use of space
  • -Determine the high-water mark
• Describe the use of Oracle Block parameters
• Recover space from sparsely populated segments
• Describe and detect chaining and migration of Oracle blocks
• Perform index reorganization