索引反向使用案例，加index_desc hint-优快云博客

本文通过对比不同SQL查询语句的执行计划，展示了如何利用索引提高查询效率。具体包括使用不同索引提示的方法及其对执行时间和资源消耗的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

此处引用梁老师例子。

转载于:https://www.cnblogs.com/Clark-cloud-database/p/7818271.html