.当按照要对数据进行排序时,如果排序列为索引列,则优化器会略去排序的步骤,因为索引列本身已经做了排序的工作。
SQL> select * from employees order by employee_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2186312383
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | EMP_EMP_ID_PK | 107 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
SQL> select * from employees order by employee_id desc ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2761389396
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| EMP_EMP_ID_PK | 107 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
注意到当进行降序排列时,优化器自动选择了索引列的降序扫描 INDEX FULL SCAN DESCENDING。而在9i版本中,oracle却远没有如此的智能:
SQL> select * from employees order by employee_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=107 Bytes=727
6)
1 0 SORT (ORDER BY) (Cost=5 Card=107 Bytes=7276)
2 1 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Byte
s=7276)
可见,采用的是全表扫描这样低效率的操作,只有加hints使用索引的情况下,才会利用索引列的排序,而且如果需要进行降序排列,oracle不会选择索引的降序扫描
SQL> select /*+ index(employees,emp_emp_id_pk) */ * from employees order by employee_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=826 Card=107 Bytes=7
276)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=826 Car
d=107 Bytes=7276)
2 1 INDEX (FULL SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE) (Cost=26 C
ard=107)
SQL> select /*+ index(employees,emp_emp_id_pk) */ * from employees order by employee_id desc;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=829 Card=107 Bytes=7
276)
1 0 SORT (ORDER BY) (Cost=829 Card=107 Bytes=7276)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=826 C
ard=107 Bytes=7276)
3 2 INDEX (FULL SCAN) OF 'EMP_EMP_ID_PK' (UNIQUE) (Cost=26
Card=107)
显然,即使在加hints强行使用索引扫描的情况下,如果选择了降序排列,仍然要进行排序操作,而在10g中,是不需要任何排序操作的,因为在进行索引扫描时,已经采用了降序扫描。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-538095/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-538095/