INDEX RANGE SCAN DESCENDING的邏輯讀問題

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。