Oracle之index_desc索引之排序升降

/*  
  结论：索引能够消除排序，这是之前学过的内容，但是如果排序是部分升序部分降序，就必须建对应部分升降序的索引，否则无法用这个来消除排序。
        比如order by col1 desc col2 asc，我们可以建(col1 desc,col2 asc)的索引。
        值得一提的是，如果你的语句变成 order by col1 asc col2 desc，之前的(col1 desc,col2 asc)的索引依然可以起到避免排序的作用DESCING。
        这在之前的课程中描述过，请同学们自行复习。不要为此多建无意义的索引。
*/


drop table t purge;
create table t as select * from dba_objects where object_id is not null ;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_t on t (owner,object_id);
alter table t modify owner not null;
alter table t modify object_id  not null;


set linesize 1000
set autotrace traceonly


--听说order by 列有索引可以消除排序，测试发现，Oracle选择不用索引，排序依然存在，索引去哪儿？
select  * from t a order by owner desc ,object_type asc;
执行计划
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   398K|    78M|       | 19133   (1)| 00:03:50 |
|   1 |  SORT ORDER BY     |      |   398K|    78M|    94M| 19133   (1)| 00:03:50 |
|   2 |   TABLE ACCESS FULL| T    |   398K|    78M|       |  1177   (1)| 00:00:15 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4209  consistent gets
          0  physical reads
          0  redo size
   13981752  bytes sent via SQL*Net to client
     215080  bytes received via SQL*Net from client
      19517  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     292740  rows processed


      
--换个思路，建如下索引      
drop index idx_t;
create index idx_t on t(owner desc,object_type asc);


--哦，索引再这，效率果然提高了,COST比未用索引导致排序的代价19133低，是14687。
select  * from t a order by owner desc ,object_type asc;
执行计划
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   398K|    78M| 14687   (1)| 00:02:57 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   398K|    78M| 14687   (1)| 00:02:57 |
|   2 |   INDEX FULL SCAN           | IDX_T |   398K|       |  1085   (1)| 00:00:14 |
-------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      52710  consistent gets
          0  physical reads
          0  redo size
   13821025  bytes sent via SQL*Net to client
     215080  bytes received via SQL*Net from client
      19517  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     292740  rows processed