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