复合索引 调整字段顺序 Cost 降低 的验证

Aug 5th 2012.
===================================================== 
p105367@O01FDR1> create table tb_obj as select * from mdb_admin.trade_client_tbl_arc;

Table created.

p105367@O01FDR1> create index i_entry_update_ref_id on tb_obj(entry_dt,update_dt,ref_id);

Index created.

p105367@O01FDR1> create index i_ref_id_entry_update on tb_obj(ref_id,entry_dt,update_dt);

Index created.

p105367@O01FDR1> exec dbms_stats.gather_table_stats('p105367','TB_OBJ',cascade=>true);

PL/SQL procedure successfully completed.

p105367@O01FDR1> col segment_name format a30
p105367@O01FDR1> select segment_name, bytes, blocks, extents from user_segments where segment_name= upper('i_entry_update_ref_id');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I_ENTRY_UPDATE_REF_ID              975689        120         15

p105367@O01FDR1> select segment_name, bytes, blocks, extents from user_segments where segment_name= upper('i_ref_id_entry_update');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I_REF_ID_ENTRY_UPDATE              975689        120         15

p105367@O01FDR1> @idx_stat.sql
Enter value for input_table_name: tb_obj
Enter value for owner: p105367

BLEV IDX_NAME                        LEAF_BLKS   DST_KEYS LEAF_PER_KEY DATA_PER_KEY CLUST_FACT LAST_ANAL TAB_BLOCKS   TAB_ROWS
---- ------------------------------ ---------- ---------- ------------ ------------ ---------- --------- ---------- --------------------------------
   1 I_ENTRY_UPDATE_REF_ID                 109      21598            1            1                   4481                 05-AUG-12       1022      21598
   1 I_REF_ID_ENTRY_UPDATE                 109      21598            1            1                   2254                 05-AUG-12       1022      21598
  
从上面的例子中可以知道，尽管两个复合索引所耗用的block数以及bytes是一致的，索引的高度也是一致的，然而索引上的聚簇因子是不同的，
第一个索引的聚簇因子为4481，第二个索引的聚簇 因子为 2254，相差一半。
如果CLUSTERING_FACTOR比较接近NUM_ROWS，说明如果扫描整个表，每次都要根据Index来读取相应行的RowID，这个时候的IO操作很多，自然检索时间会比较长。
如果数据有序的话，CLUSTERING_FACTOR比较接近BLOCKS，说明相邻的数据在一个块中，减少了IO操作数量，自然检索时间会大大降低。

在Oracle中的有一个视图user_indexes中有一个列clustering_factor，这个值的内容就是如果访问表的整个表数据，会造成多少次数据库IO。

A：如果这个值与块数接近，则说明表相当有序，得到了很好的组织。在这种情况下，同一个叶子块中的索引条目可能指向同一个数据块中的行。
B：如果这个值与行数接近，表的次序可能就是非常随机的。在这种情况下，同一个叶子块上的索引条目不太可能指向同一个数据块上的行。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Oracle 手册中关于CLUSTERING_FACTOR的说明：
Indicates the amount of order of the rows in the table based on the values of the index.
If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
可以把聚簇因子看作是通过索引读取整个表时对表执行的逻辑I/O次数。也就是说局促因子指示了表相对于索引本身的有序程度。当oracle对索引结构执行区间扫描时，
如果它发现索引中的下一行与前一行在同一个数据块上，就不会再执行另一个I/O从缓冲区缓存中获得表块。它已经有了表块的一个句柄，只需要直接使用就行了。
不过，如果下一行不在同一个块上，就会释放当前的这个块，而执行另一个物理I/O在缓冲区缓存存放要处理的下一个块。

对于一个表来说，一般只有一个索引能够有合适的聚簇因子！表中的行可能只以一种方式排序。

因此epcis 库中，PA18CLM.IND_T_TASK_DETAIL_TM_ID_RLNO索引调整之后I/O变小是正常的。

=====================================================
复合索引的一般规则：
=====================================================
其次，关于复合索引，很明显HANDLER_ID 这个字段的选择性明显高于其余两个字段。
复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
 
 1、正确选择复合索引中的主列字段，一般是选择性较好的字段；

 2、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

  3、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

  4、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

  5、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22578826/viewspace-741009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22578826/viewspace-741009/