目录反向利用案例,加index_desc hint

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

那里引用梁老师例子。

 

 

相关文章