《Oracle编程艺术》学习笔记(31)-什么情况下适合使用B*树索引

B*树索引->http://blog.csdn.net/fw0124/article/details/6902151
有两种适合使用B*树索引的方法，
1）仅通过索引回答一个查询
索引包含了足够的信息来回答整个查询，根本不用去访问表。在这种情况下，索引则用作一个“较瘦“版本的表。

2）通过读索引来访问表中的少部分行
此时应当只访问问表中很少的一部分行（只占一个很小的百分比）。如果需要通过索引访问大量的行，那么与全表扫描相比，通过索引反而要花更多时间。
这是因为，一方面，增加了读取索引本身的数据块的I/O，另一方面，索引按索引键的顺序存储，会按键的有序顺序进行访问，索引指向的块则随机地存储在堆中，因此通过索引访问表时，会执行大量分散、随机的I/O。
当Oracle对索引结构执行区间扫描时，如果它发现索引中的下一行和前一行在同一个数据库块上，就不会再执行另一个I/O从缓冲区缓存中获得表块，它已经有表块的一个句柄，只需直接使用就可以了。不过，如果下一行不在同一个块上，就会释放当前的这个块，而执行另一个I/O从缓冲区缓存获取要处理的下一个块。因此,数据在磁盘上如何物理存储,也会极大影响到通过索引访问数据的开销。

下面创建表格COLOCATED，按照主键X递增(1->1000)的顺序插入，因此在磁盘上一般也是按照主键顺序存放（并不能保证一定如此，若要保证这一点，需要使用IOT）。
另外创建表格DISORGANIZED，把COLOCATED表格中的数据全部插入此表，但是按照随机数列Y的顺序插入，这样对于主键X来说，表格中的数据是分散的。

tony@ORA11GR2> create table colocated as 2 select rownum x, dbms_random.random y 3 from dual connect by rownum <= 1000; Table created. tony@ORA11GR2> alter table colocated 2 add constraint colocated_pk primary key(x); Table altered. tony@ORA11GR2> create table disorganized as 2 select x,y from colocated order by y; Table created. tony@ORA11GR2> alter table disorganized 2 add constraint disorganized_pk primary key(x); Table altered. tony@ORA11GR2> exec dbms_stats.gather_table_stats(user, 'COLOCATED', cascade=>true); PL/SQL procedure successfully completed. tony@ORA11GR2> exec dbms_stats.gather_table_stats(user, 'DISORGANIZED', cascade=>true); PL/SQL procedure successfully completed.

先来看看访问表格中的行的比例对查询计划的影响。

tony@ORA11GR2> select * from colocated where x between 100 and 400; Execution Plan ---------------------------------------------------------- Plan hash value: 1550765370 ----------------------------------------------------------------------------------- | Id | Operation |Name |Rows |Bytes|Cost (%CPU)|Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 302| 3322| 3 (0)|00:00:01| | 1 | TABLE ACCESS BY INDEX ROWID|COLOCATED | 302| 3322| 3 (0)|00:00:01| |* 2 | INDEX RANGE SCAN |COLOCATED_PK| 302| | 2 (0)|00:00:01| ----------------------------------------------------------------------------------- tony@ORA11GR2> select * from colocated where x between 100 and 500; Execution Plan ---------------------------------------------------------- Plan hash value: 4293073267 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 402 | 4422 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| COLOCATED | 402 | 4422 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------

可以看到访问30%的行的时候，Oracle选择的查询计划是INDEX RANGE SCAN，访问40%的行的时候，查询计划变成了TABLE ACCESS FULL。

tony@ORA11GR2> select * from disorganized where x between 100 and 200; Execution Plan ---------------------------------------------------------- Plan hash value: 2727546897 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 102 | 1122 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DISORGANIZED | 102 | 1122 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------

但是对于DISORGANIZED表格，即使只访问10%的表格，Oracle选择的查询计划也是全表扫描。
为什么会有这个差别？先来了解聚簇因子的概念。

聚簇因子（CLUSTERING_FACTOR）

USER_INDEXES视图中有一列叫做聚簇因子，它的含义是指示表中行的有序程度：
· 如果这个值与块数接近，则说明表相当有序，得到了很好的组织，在这种情况下，同一个叶子块中的索引条目可能指向同一个数据块上的行。
· 如果这个值与行数接近，表的次序可能就是非常随机的。在这种情况下，同一个叶子块上的索引条目不太可能指向同一个数据块上的行。

tony@ORA11GR2> select idx.index_name, tbl.num_rows, tbl.blocks, idx.clustering_factor 2 from user_indexes idx, user_tables tbl 3 where idx.table_name=tbl.table_name 4 and idx.index_name in ('COLOCATED_PK', 'DISORGANIZED_PK'); INDEX_NAME NUM_ROWS BLOCKS CLUSTERING_FACTOR ---------------------------------------- ---------- ---------- ----------------- COLOCATED_PK 1000 6 3 DISORGANIZED_PK 1000 6 587

可以看到DISORGANIZED表的聚簇因子相当大，因此Oracle选择全表扫描的计划。
可以把聚簇因子看作是通过索引读取整个表时对表执行的逻辑I/O次数。
（COLOCATED表的聚簇因子小于表中的块数。这是因为使用了ASSM管理的表空间，COLOCATED表中在HWM之下有一些未格式化的块，其中未包含数据，而且ASSM 本身也使用了一些块来管理空间，索引区间扫描中不会读取这些块。）

下面通过TKPROF查看，对于上面的2张表，通过索引读取整个表各需要执行多少次I/O。
TKPROF->http://blog.csdn.net/fw0124/article/details/6899162

select count(y) from (select /*+ index(disorganized, disorganized_pk) */ * from disorganized) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 590 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 590 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 85 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=590 pr=0 pw=0 time=0 us) 1000 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=590 pr=0 pw=0 time=11738 us cost=590 size=8000 card=1000) 1000 INDEX FULL SCAN DISORGANIZED_PK (cr=3 pr=0 pw=0 time=2247 us cost=3 size=0 card=1000)(object id 73839) ******************************************************************************** SQL ID: 3tv5g4y1p2tft Plan Hash: 3483305348 select count(y) from (select /*+ index(colocated, colocated_pk) */ * from colocated) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 0 6 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.00 0 6 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 85 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us) 1000 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=6 pr=0 pw=0 time=5994 us cost=6 size=8000 card=1000) 1000 INDEX FULL SCAN COLOCATED_PK (cr=3 pr=0 pw=0 time=1998 us cost=3 size=0 card=1000)(object id 73837)

可以看到，
对于DISORGANIZED表，总共执行590（cr=590）次一致读，如果减去索引扫描的3次，访问表格的一致读次数为587；
对于COLOCATED表，总共执行6（cr=6）次一致读，如果减去索引扫描的3次，访问表格的一致读次数为3；
和聚簇因子相等。

总结一下，索引并不一定总是合适的访问方法。影响优化器是否采用索引因素很多，包括数据的物理存储布局。
如果完全适用索引就可以回答查询，那么访问大量的行就是有意义的，
如果使用索引来访问表（即查询计划中的TABLE ACCESS BY INDEX ROWID），那么只适合访问少量的行。