《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），那么只适合访问少量的行。