SQL> create table test_desc (id number not null,name varchar2(16)) tablespace users;
Table created.
SQL> create index test_id_desc_idx on test_desc(id desc) tablespace idx;
Index created.
Oracle默认情况下是升序索引,这里定义了降序索引。
[@more@]我们看一下select会怎么样
SQL> select * from test_desc where id<10;
ID NAME
---------- ----------------
9 d
8 d
7 d
6 d
5 d
4 d
3 d
2 d
1 d
下面我们求id小于100的前5条记录
SQL> l
1 select * from
2* (select id,name,rownum rnk from test_desc where id<101) where rnk<6
SQL> /
ID NAME RNK
---------- ---------------- ----------
100 d 1
99 d 2
98 d 3
97 d 4
96 d 5
Execution Plan
----------------------------------------------------------
Plan hash value: 328368902
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 3600 | 2 (0)| 00:00:01 |
|* 1 | VIEW | | 100 | 3600 | 2 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST_DESC | 100 | 2300 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TEST_ID_DESC_IDX | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNK"<6)
4 - access(SYS_OP_DESCEND("ID")>HEXTORAW('3DFDFDFF') )
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("ID"))<101)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
592 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)
5 rows processed
可以看到执行计划避免了不必要的排序
当我们查询salary小于5000的前10人时可以应用降序索引解决问题.
为emp(salary)创建降序索引
select * from (
select xxx,rownum rnk from emp where salary<5000)
where rnk<11;
即可.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/76065/viewspace-831997/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/76065/viewspace-831997/