这是一个创建于 2586 天前的主题，其中的信息可能已经有所发展或是发生改变。
走索引的执行计划:
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
 |
1
adsun 2017-10-18 18:37:28 +08:00 via Android  1
我也遇到过这种问题，两个相同结构的 DB，数据量大的那个没有用到索引，如果使用使用强制索引，会更慢，可能是 Oracle 自己的优化
|
 |
2
fanqianger 2017-10-18 21:19:27 +08:00 1
需要做一下表分析，oracle 是根据收集到的统计数据来决定执行计划的。
|
 |
3
wdlth 2017-10-18 21:46:00 +08:00 1
更新统计信息后再试试
|
 |
4
jtn007 2017-10-18 21:54:18 +08:00 1
自动优化，有可能用了索引一样也要全表扫描，所以就不使用了
|
 |
5
sheldoner 2017-10-18 21:59:00 +08:00 1
哇，好炫酷的样子，请问是什么工具能看到执行时间？
|
 |
6
sagaxu 2017-10-19 08:16:15 +08:00 via Android 1
rbo 和 cbo，现代 db 都是 cbo
|
 |
7
dltsgl OP 全局索引改成本地索引就好了，不知道为啥
|
Select Language