[20170210]索引范围扫描3.txt-优快云博客

本文通过具体示例详细解析了Oracle数据库中索引范围扫描的工作原理，并解释了如何通过执行计划来理解索引扫描的过程。文章重点介绍了在特定条件下，Oracle如何有效地利用索引进行数据检索。

[20170210]索引范围扫描3.txt

--昨天写了一篇索引范围扫描文章,链接:http://blog.itpub.net/267265/viewspace-2133289/
--才想起来我以前理解的一个错误,链接:http://blog.itpub.net/267265/viewspace-772371/
--还是通过例子来说明:

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t (id number,name varchar2(20));
insert into t select rownum id ,lpad('x',20,'x') name from dual connect by level <=100;
commit ;
create unique index i_t_id on t(id);

--分析略.

2.测试
SCOTT@book> alter session set statistics_level=all;
Session altered.

SCOTT@book> Select * from t where id between 4 and 100 ;
...

SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0z6wka791vxz2, child number 0
-------------------------------------
Select * from t where id between 4 and 100
Plan hash value: 4153437776
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                   | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |       |     2 (100)|          |     97 |00:00:00.01 |       4 |
|   1 | TABLE ACCESS BY INDEX ROWID| T      |      1 |     98 | 2352 |     2   (0)| 00:00:01 |     97 |00:00:00.01 |       4 |
|* 2 |   INDEX RANGE SCAN          | I_T_ID |      1 |     98 |       |     1   (0)| 00:00:01 |     97 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=4 AND "ID"<=100)

--//逻辑读是4,原来我一直以为是12C执行计划中TABLE ACCESS BY INDEX ROWID BATCHED的功劳,实际上昨天的测试让我知道11G也应该是这样.
--//看来学习不认真,我一直以为oracle像上面的操作是读一次叶子,读一个表块记录,再读一次叶子,再读一个表块记录,这样来操作,实际上这里面还与
--//arraysize有关.在10g下测试也一样.