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