浅谈Oracle B-TREE索引扫描方式(上)

1.Index Unique Scans

适用场景:主键或唯一索引的等值匹配,最多只会返回一行数据,是单块读
索引唯一扫描的工作方式

     浅谈Oracle B-TREE索引扫描方式(上)

 

示例:

SYS@pudge> create table xiaom.test1(id number primary key,create_time date default sysdate);

Table created.

SYS@pudge> begin
2 for i in 1 .. 100000 loop
3 insert into xiaom.test1(id)
4 values
5 (i);
6 end loop;
7 commit;
8 end;
9 /

PL/SQL procedure successfully completed.

SYS@pudge> set autotrace on
SYS@pudge> select * from xiaom.test1 where id = 5000;

ID CREATE_TIME
---------- -------------------
5000 2020-08-19 15:49:27

Execution Plan
----------------------------------------------------------
Plan hash value: 3480502009

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 22 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005694 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=5000)

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
469 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SYS@pudge> /

 

2.Index Range Scans

适用场景:1.主键或唯一索引的范围查询 2.非唯一性索引的等值和范围查询
工作方式:1)读取根节点  2)读取分支节点 3)交替执行一下步骤  a.读取叶子块获取rowid  b.通过rowid读取数据行浅谈Oracle B-TREE索引扫描方式(上)

 

示例(以唯一性索引的范围匹配查询为例):

SYS@pudge> select * from xiaom.test1 where id >=5000 and id < 5010;

ID CREATE_TIME
---------- -------------------
5000 2020-08-19 15:49:27
5001 2020-08-19 15:49:27
5002 2020-08-19 15:49:27
5003 2020-08-19 15:49:27
5004 2020-08-19 15:49:27
5005 2020-08-19 15:49:27
5006 2020-08-19 15:49:27
5007 2020-08-19 15:49:27
5008 2020-08-19 15:49:27
5009 2020-08-19 15:49:27

10 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3210885398

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 220 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 10 | 220 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C005694 | 10 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=5000 AND "ID"<5010)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
808 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

3.Index Full Scans

使用场景:按照顺序读取整个索引,是一种相对比较差劲的扫描方式(单块读)
工作方式:数据库读取根块,然后在索引的左侧向下导航(如果进行向下的完整扫描,则向右导航),直到到达叶块为止。然后数据库到达一个叶块,扫描按索引顺序遍历索引的底部,一次跨一个块。数据库使用单块I / O而不是多块I / O。浅谈Oracle B-TREE索引扫描方式(上)

 

示例:

SYS@pudge> select id from xiaom.test1 order by id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 181745691

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97463 | 1237K| 245 (0)| 00:00:03 |
| 1 | INDEX FULL SCAN | SYS_C005694 | 97463 | 1237K| 245 (0)| 00:00:03 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
6900 consistent gets
0 physical reads
0 redo size
1829390 bytes sent via SQL*Net to client
73850 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed

 

原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

浅谈Oracle B-TREE索引扫描方式(上)

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » 浅谈Oracle B-TREE索引扫描方式(上)
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏