046.【实验：索引的升序降序扫描】

【1.升序】INDEX FULL SCAN
SQL> select /*+ index_asc(t)*/ id from scott.t_group1 t where rownum<10;

ID
----------
1
1
2
2
3
3
4
4
5

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 684891757

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 45 | 3 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| IDX_ID | 9 | 45 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
488 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

【2.降序扫描】INDEX FULL SCAN DESCENDING
SQL> select /*+ index_desc(t)*/ id from scott.t_group1 t where rownum<10;

ID
----------
250000
249999
249998
249997
249996
249995
249994
249993
249992

9 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2785056241

--------------------------------------------------------------------------------
------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
------

| 0 | SELECT STATEMENT | | 9 | 45 | 3 (0)| 00:0
0:01 |

|* 1 | COUNT STOPKEY | | | | |
|

| 2 | INDEX FULL SCAN DESCENDING| IDX_ID | 9 | 45 | 3 (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
516 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7901922/viewspace-1060029/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7901922/viewspace-1060029/