链接
http://space.itpub.net/267265/viewspace-772371
写了12c下在范围扫描时可能出现的TABLE ACCESS BY INDEX ROWID BATCHED，这是一种新的执行方式，能够
提高执行效率，特别在数据聚集很好的情况下。
既然是12c的一个特性应该有一个参数关闭这个特性。重复前面的例子：
1.建立测试环境:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t (id number,name varchar2(20));
Table created.
--打开3个session，分别执行如下：
-session 1:
insert into t values (1,lpad('a',20,'a'));
commit ;
-session 2:
insert into t values (2,lpad('b',20,'b'));
commit ;
-session 3:
insert into t values (3,lpad('c',20,'c'));
commit ;
insert into t select rownum+3 id ,lpad('x',20,'x') name from dual connect by level commit ;
--这样操作可以导致id=1在一个数据块id=2,3在另外的数据块。
SCOTT@test01p> select rowid ,t.* from t where id between 1 and 3;
ROWID ID NAME
------------------ ---------- --------------------
AAAWxnAAJAAAAC1AAA 1 aaaaaaaaaaaaaaaaaaaa
AAAWxnAAJAAAAC3AAA 2 bbbbbbbbbbbbbbbbbbbb
AAAWxnAAJAAAAC3AAB 3 cccccccccccccccccccc
COTT@test01p> @lookup_rowid AAAWxnAAJAAAAC1AAA
OBJECT FILE BLOCK ROW DBA
--------- ---------- ---------- ---------- --------------------
93287 9 181 0 9,181
COTT@test01p> @lookup_rowid AAAWxnAAJAAAAC3AAA
OBJECT FILE BLOCK ROW DBA
--------- ---------- ---------- ---------- --------------------
93287 9 183 0 9,183
SCOTT@test01p> create unique index i_t_id on t(id);
Index created.
--分析表
SCOTT@test01p> execute dbms_stats.gather_table_stats(user,'t');
PL/SQL procedure successfully completed.
2.测试
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3uy89r3c5z5yy, child number 0
-------------------------------------
select * from t where id between 1 and 100
Plan hash value: 3446268138
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 100 | 3 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 100 | 1 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"
--即使扫描全部数据，选择的也是使用索引。
SYS@test01p> @hide _optimizer_batch_table_access_by_rowid
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_optimizer_batch_table_access_by_rowid%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------------- ------------------------------------------ -------------- -------------- ------------
_optimizer_batch_table_access_by_rowid enable table access by ROWID IO batching TRUE TRUE TRUE
SCOTT@test01p> alter session set "_optimizer_batch_table_access_by_rowid"=false;
Session altered.
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3uy89r3c5z5yy, child number 1
-------------------------------------
select * from t where id between 1 and 100
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 3 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 100 | 1 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"
--回到原来的模式，奇怪的是为什么选择的还是INDEX RANGE SCAN+ TABLE ACCESS BY INDEX ROWID扫描呢？
--正常应该选择全表扫描。
SCOTT@test01p> set autot traceonly
SCOTT@test01p> select * from t where id between 1 and 100;
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 2400 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 100 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=1 AND "ID"
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
3548 bytes sent via SQL*Net to client
544 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
--奇怪逻辑读还是4.难道oracle改进了什么？能力如此，oracle许多东西不了解，那位知道给出答案！