set linesize 1000

set autotrace traceonly


drop table t purge;

create table t as select * from dba_objects;

create index idx_t on t (owner,object_id);

alter table t modify owner not null;


select /*+index(a,idx_t)*/ * from t a order by owner desc ,object_type asc;

执行计划

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

| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |       | 74796 |    14M|       |  5600   (1)| 00:01:08 |

|   1 |  SORT ORDER BY               |       | 74796 |    14M|    17M|  5600   (1)| 00:01:08 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T     | 74796 |    14M|       |  2232   (1)| 00:00:27 |

|   3 |    INDEX FULL SCAN           | IDX_T | 74796 |       |       |   223   (1)| 00:00:03 |

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

统计信息

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

          0  recursive calls

          0  db block gets

       2229  consistent gets

          0  physical reads

          0  redo size

    3463715  bytes sent via SQL*Net to client

      54052  bytes received via SQL*Net from client

       4878  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      73154  rows processed

      

      

drop index idx_t;

create index idx_t on t(owner desc,object_type asc);


select /*+index(a,idx_t)*/ * from t a order by owner desc ,object_type asc;

执行计划

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

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |       | 74796 |    14M|  3463   (1)| 00:00:42 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 74796 |    14M|  3463   (1)| 00:00:42 |

|   2 |   INDEX FULL SCAN           | IDX_T | 74796 |       |   272   (1)| 00:00:04 |

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

统计信息

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

          0  recursive calls

          0  db block gets

      12975  consistent gets

          0  physical reads

          0  redo size

    3400784  bytes sent via SQL*Net to client

      54052  bytes received via SQL*Net from client

       4878  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      73154  rows processed

      

      

select /*+index(a,idx_t)*/ * from t a order by owner asc ,object_type desc;

执行计划

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

| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |       | 74796 |    14M|       |  6831   (1)| 00:01:22 |

|   1 |  SORT ORDER BY               |       | 74796 |    14M|    17M|  6831   (1)| 00:01:22 |

|   2 |   TABLE ACCESS BY INDEX ROWID| T     | 74796 |    14M|       |  3463   (1)| 00:00:42 |

|   3 |    INDEX FULL SCAN           | IDX_T | 74796 |       |       |   272   (1)| 00:00:04 |

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

统计信息

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

          0  recursive calls

          0  db block gets

       3459  consistent gets

          0  physical reads

          0  redo size

    3439096  bytes sent via SQL*Net to client

      54052  bytes received via SQL*Net from client

       4878  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      73154  rows processed


      

select /*+index_desc(a,idx_t)*/ * from t a order by owner asc ,object_type desc;

执行计划

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

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |       | 74796 |    14M|  3463   (1)| 00:00:42 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 74796 |    14M|  3463   (1)| 00:00:42 |

|   2 |   INDEX FULL SCAN DESCENDING| IDX_T | 74796 |       |   272   (1)| 00:00:04 |

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

统计信息

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

          0  recursive calls

          0  db block gets

      12968  consistent gets

          0  physical reads

          0  redo size

    3400803  bytes sent via SQL*Net to client

      54052  bytes received via SQL*Net from client

       4878  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      73154  rows processed