oracle 降序索引 创建,降序索引应用在Top N问题上的应用

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;

即可.