[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下测试也一样.
