itpub:http://www.itpub.net/thread-1335784-1-1.html
INDEX RANGE SCAN DESCENDING的逻辑读126明显大于INDEX RANGE SCAN 5，但优化器依然选择了INDEX RANGE SCAN DESCENDING
这里先不讨论优化器的选择，仅仅看看是否INDEX RANGE SCAN DESCENDING的逻辑读会高的这么厉害
下面是测试代码：
[@more@]SQL> create table emp (id,name) as select object_id,object_name from dba_objects
;
表已创建。
SQL> create index t_idx_emp on emp(id);
索引已创建。
SQL> set arraysize 5000
SQL> select id,name from emp where id between 100 and 1000;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 162245774
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
22102 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> select id,name from emp where id between 100 and 1000 order by id;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 162245774
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
22102 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> select id,name from emp where id between 100 and 1000 order by id desc;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 1596401432
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
22102 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> select /*+ index(emp) */ id,name from emp where id between 100 and 1000 ord
er by id desc;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 1321738087
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 8 (13)|00:00:01 |
| 1 | SORT ORDER BY | | 845 | 66755 | 8 (13)|00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
22078 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> set arraysize 15
SQL> select id,name from emp where id between 100 and 1000;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 162245774
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
29382 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> select id,name from emp where id between 100 and 1000 order by id desc;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 1596401432
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
29382 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> select id,name from emp where id between 100 and 1000 order by id;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 162245774
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
121 consistent gets
0 physical reads
0 redo size
29382 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
845 rows processed
SQL> select /*+ index(emp) */ id,name from emp where id between 100 and 1000 ord
er by id desc;
已选择845行。
执行计划
----------------------------------------------------------
Plan hash value: 1321738087
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 845 | 66755 | 8 (13)|00:00:01 |
| 1 | SORT ORDER BY | | 845 | 66755 | 8 (13)|00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 845 | 66755 | 7 (0)|00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX_EMP | 845 | | 3 (0)|00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">=100 AND "ID"<=1000)
Note
-----
- dynamic sampling used for this statement (level=4)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
26058 bytes sent via SQL*Net to client
1031 bytes received via SQL*Net from client
58 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
845 rows processed
对于select /*+ index(emp) */ id,name from emp where id between 100 and 1000 ord
er by id desc;
貌似不受arraysize的影响，不是经过了特殊的优化，就是个BUG。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/19423/viewspace-1036681/ ，如需转载，请注明出处，否则将追究法律责任。
