oracle索引字段降序还是升序,索引学习四--降序索引

1. 建测试表s_table，并在empno和ename列上创建唯一索引

table s_table as select * from test where empno<10;

Table created.unique index idx_s on s_table(empno,ename);

Index created.empno,ename from s_table where empno between 1 and 5

2  and ename is not null order by empno,ename;

Execution Plan

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

0      SELECT STATEMENT ptimizer=CHOOSE

1    0   INDEX (RANGE SCAN) OF 'IDX_S' (UNIQUE)

上面的查询中，由于索引中的两列已经做了排序，因此在生成的执行计划中，没有排序操作。

2. 改变查询，按照empno降序，ename升序来查询。

empno,ename from s_table where empno between 1 and 5

2  and ename is not null order by empno desc,ename asc;

Execution Plan

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

0      SELECT STATEMENT ptimizer=CHOOSE

1    0   SORT (ORDER BY)

2    1