你好,游客 登录 注册 搜索
背景:
阅读新闻

《Oracle编程艺术》学习笔记

什么情况下适合使用B*树索引

[日期:2012-01-11] 来源:Linux社区  作者:fw0124 [字体: ]

B*树索引-> http://www.linuxidc.com/Linux/2012-01/51264p30.htm
有两种适合使用B*树索引的方法,
1)仅通过索引回答一个查询
索引包含了足够的信息来回答整个查询,根本不用去访问表。在这种情况下,索引则用作一个“较瘦“版本的表。

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

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

[sql]
  1. tony@ORA11GR2> create table colocated as  
  2. select rownum x, dbms_random.random y  
  3. from dual connect by rownum <= 1000;  
  4. Table created.  
  5.   
  6. tony@ORA11GR2> alter table colocated  
  7. add constraint colocated_pk primary key(x);  
  8. Table altered.  
  9.   
  10. tony@ORA11GR2> create table disorganized as  
  11. select x,y from colocated order by y;  
  12. Table created.  
  13.   
  14. tony@ORA11GR2> alter table disorganized  
  15. add constraint disorganized_pk primary key(x);  
  16. Table altered.  
  17.   
  18. tony@ORA11GR2> exec dbms_stats.gather_table_stats(user'COLOCATED'cascade=>true);  
  19. PL/SQL procedure successfully completed.  
  20.   
  21. tony@ORA11GR2> exec dbms_stats.gather_table_stats(user'DISORGANIZED'cascade=>true);  
  22. PL/SQL procedure successfully completed.  

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

[sql]
 
  1. tony@ORA11GR2> select * from colocated where x between 100 and 400;  
  2. Execution Plan  
  3. ----------------------------------------------------------   
  4. Plan hash value: 1550765370  
  5.   
  6. -----------------------------------------------------------------------------------   
  7. | Id  | Operation                   |Name        |Rows |Bytes|Cost (%CPU)|Time    |  
  8. -----------------------------------------------------------------------------------   
  9. |   0 | SELECT STATEMENT            |            |  302| 3322|    3   (0)|00:00:01|  
  10. |   1 |  TABLE ACCESS BY INDEX ROWID|COLOCATED   |  302| 3322|    3   (0)|00:00:01|  
  11. |*  2 |   INDEX RANGE SCAN          |COLOCATED_PK|  302|     |    2   (0)|00:00:01|  
  12. -----------------------------------------------------------------------------------   
  13.   
  14. tony@ORA11GR2> select * from colocated where x between 100 and 500;  
  15.   
  16. Execution Plan  
  17. ----------------------------------------------------------   
  18. Plan hash value: 4293073267  
  19.   
  20. -------------------------------------------------------------------------------   
  21. | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |  
  22. -------------------------------------------------------------------------------   
  23. |   0 | SELECT STATEMENT  |           |   402 |  4422 |     3   (0)| 00:00:01 |  
  24. |*  1 |  TABLE ACCESS FULL| COLOCATED |   402 |  4422 |     3   (0)| 00:00:01 |  
  25. -------------------------------------------------------------------------------  

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

[sql]
  1. tony@ORA11GR2> select * from disorganized where x between 100 and 200;  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------   
  5. Plan hash value: 2727546897  
  6.   
  7. ----------------------------------------------------------------------------------   
  8. | Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. ----------------------------------------------------------------------------------   
  10. |   0 | SELECT STATEMENT  |              |   102 |  1122 |     3   (0)| 00:00:01 |  
  11. |*  1 |  TABLE ACCESS FULL| DISORGANIZED |   102 |  1122 |     3   (0)| 00:00:01 |  
  12. ----------------------------------------------------------------------------------  

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

聚簇因子(CLUSTERING_FACTOR)

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

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

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

下面通过TKPROF查看,对于上面的2张表,通过索引读取整个表各需要执行多少次I/O。
TKPROF-> http://www.linuxidc.com/Linux/2012-01/51264p7.htm

[plain]
  1. select count(y)   
  2. from  
  3. (select /*+ index(disorganized, disorganized_pk) */ * from disorganized)  
  4.   
  5.   
  6. call count cpu elapsed disk query current rows  
  7. ------- ------ -------- ---------- ---------- ---------- ---------- ----------  
  8. Parse 1 0.00 0.00 0 0 0 0  
  9. Execute 1 0.00 0.00 0 0 0 0  
  10. Fetch 2 0.00 0.00 0 590 0 1  
  11. ------- ------ -------- ---------- ---------- ---------- ---------- ----------  
  12. total 4 0.00 0.00 0 590 0 1  
  13.   
  14. Misses in library cache during parse: 1  
  15. Optimizer mode: ALL_ROWS  
  16. Parsing user id: 85   
  17.   
  18. Rows Row Source Operation  
  19. ------- ---------------------------------------------------  
  20. 1 SORT AGGREGATE (cr=590 pr=0 pw=0 time=0 us)  
  21. 1000 TABLE ACCESS BY INDEX ROWID DISORGANIZED (cr=590 pr=0 pw=0 time=11738 us cost=590 size=8000 card=1000)  
  22. 1000 INDEX FULL SCAN DISORGANIZED_PK (cr=3 pr=0 pw=0 time=2247 us cost=3 size=0 card=1000)(object id 73839)  
  23.   
  24. ********************************************************************************  
  25.   
  26. SQL ID: 3tv5g4y1p2tft  
  27. Plan Hash: 3483305348  
  28. select count(y)   
  29. from  
  30. (select /*+ index(colocated, colocated_pk) */ * from colocated)  
  31.   
  32.   
  33. call count cpu elapsed disk query current rows  
  34. ------- ------ -------- ---------- ---------- ---------- ---------- ----------  
  35. Parse 1 0.01 0.00 0 0 0 0  
  36. Execute 1 0.00 0.00 0 0 0 0  
  37. Fetch 2 0.00 0.00 0 6 0 1  
  38. ------- ------ -------- ---------- ---------- ---------- ---------- ----------  
  39. total 4 0.01 0.00 0 6 0 1  
  40.   
  41. Misses in library cache during parse: 1  
  42. Optimizer mode: ALL_ROWS  
  43. Parsing user id: 85   
  44.   
  45. Rows Row Source Operation  
  46. ------- ---------------------------------------------------  
  47. 1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)  
  48. 1000 TABLE ACCESS BY INDEX ROWID COLOCATED (cr=6 pr=0 pw=0 time=5994 us cost=6 size=8000 card=1000)  
  49. 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),那么只适合访问少量的行。

linux
【内容导航】
第1页:配置环境 第2页:开发成功的Oracle应用
第3页:SELECT FOR UPDATE SKIP LOCKED 第4页:Oracle体系结构
第5页:Oracle中的文件 第6页:Oracle中的文件-参数文件
第7页:Oracle中的文件-Trace文件 第8页:Oracle中的文件-警告日志文件
第9页:Oracle中的文件-数据文件 第10页:SGA,PGA和UGA
第11页:Oracle进程 第12页:Oracle的锁
第13页:Oracle的并发与多版本控制 第14页:写一致性
第15页:事务原子性 第16页:事务持久性
第17页:DEFERRABLE约束 第18页:REDO和UNDO
第19页:数据库日志模式 第20页:块清除
第21页:ORA-01555:snapshot too old 错误 第22页:表类型
第23页: 第24页:堆组织表
第25页:索引组织表 第26页:索引聚簇表
第27页:散列聚簇表 第28页:有序散列聚簇表
第29页:临时表和CBO 第30页:B*树索引
第31页:什么情况下适合使用B*树索引 第32页:位图索引(bitmap index)
第33页:基于函数的索引 第34页:数据类型-字符串和二进制串
第35页:数据类型-数值 第36页:数据类型-时间日期
第37页:数据类型-LOB 第38页:表分区
第39页:索引分区 第40页:分区的优点
相关资讯       Oracle基础教程 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款