走索引的执行计划:
Plan Hash Value : 2020907732
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 66 | 00:00:01 |
| * 1 | VIEW | | 10 | 250 | 66 | 00:00:01 |
| * 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 11 | 132 | 66 | 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 11 | 990 | 66 | 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 11 | 748 | 44 | 00:00:01 |
| 6 | NESTED LOOPS | | 11 | 506 | 22 | 00:00:01 |
| * 7 | TABLE ACCESS BY INDEX ROWID | T_PAY | 519661 | 12471864 | 10 | 00:00:01 |
| 8 | INDEX FULL SCAN DESCENDING | IDX_PAY_MAIN_ID | 12 | | 3 | 00:00:01 |
| * 9 | INDEX UNIQUE SCAN | PK_MAIN_ID | 1 | 22 | 1 | 00:00:01 |
| * 10 | INDEX RANGE SCAN | IDX_SUB_MAIN_ID | 1 | 22 | 2 | 00:00:01 |
| * 11 | INDEX RANGE SCAN | IDX_ADDR_MAIN_ID | 1 | 22 | 2 | 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("T"."RN">=1)
* 2 - filter(ROWNUM<=10)
* 7 - filter("PAY"."TYPE"='1')
* 9 - access("TMAIN"."ID"="PAY"."MAIN_ID")
* 10 - access("TMAIN"."ID"="TSUB"."MAIN_ID"(+))
* 11 - access("TMAIN"."ID"="ADDR"."MAIN_ID"(+))
不走索引的执行计划:
Plan Hash Value : 4219115835
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 250 | 24 | 00:00:01 |
| * 1 | VIEW | | 10 | 250 | 24 | 00:00:01 |
| * 2 | COUNT STOPKEY | | | | | |
| * 3 | HASH JOIN | | 11 | 990 | 24 | 00:00:01 |
| * 4 | HASH JOIN OUTER | | 97 | 6402 | 18 | 00:00:01 |
| * 5 | HASH JOIN OUTER | | 97 | 4268 | 12 | 00:00:01 |
| 6 | PARTITION RANGE ALL | | 97 | 2134 | 6 | 00:00:01 |
| 7 | TABLE ACCESS FULL | T_MAIN_HIS | 97 | 2134 | 6 | 00:00:01 |
| 8 | PARTITION RANGE ALL | | 91 | 2002 | 6 | 00:00:01 |
| 9 | TABLE ACCESS FULL | T_ADDR_HIS | 91 | 2002 | 6 | 00:00:01 |
| 10 | PARTITION RANGE ALL | | 97 | 2134 | 6 | 00:00:01 |
| 11 | TABLE ACCESS FULL | T_SUB_HIS | 97 | 2134 | 6 | 00:00:01 |
| 12 | PARTITION RANGE ALL | | 89 | 2136 | 6 | 00:00:01 |
| * 13 | TABLE ACCESS FULL | T_PAY_HIS | 89 | 2136 | 6 | 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("T"."RN">=1)
* 2 - filter(ROWNUM<=10)
* 3 - access("TMAIN"."ID"="PAY"."MAIN_ID")
* 4 - access("TMAIN"."ID"="TSUB"."MAIN_ID"(+))
* 5 - access("TMAIN"."ID"="ADDR"."MAIN_ID"(+))
* 13 - filter("PAY"."TYPE"='1')
两个查询所使用的表,除了表名后者带 HIS 外,后者都是建了分区的,不过查询条件没用到分区列,索引是完全一样的,查询时都是直接查全表的 结果后者查询时间是前者的 5-6 倍
为啥后者会全表扫描呢
PS:数据库是 ORACLE
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.