表结构几乎一样,数据一样,同样的查询,一个走索引,一个不走索引,是啥原因

2017-10-18 18:11:36 +08:00
 dltsgl

走索引的执行计划:

 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

1552 次点击
所在节点    数据库
7 条回复
adsun
2017-10-18 18:37:28 +08:00
我也遇到过这种问题,两个相同结构的 DB,数据量大的那个没有用到索引,如果使用使用强制索引,会更慢,可能是 Oracle 自己的优化
fanqianger
2017-10-18 21:19:27 +08:00
需要做一下表分析,oracle 是根据收集到的统计数据来决定执行计划的。
wdlth
2017-10-18 21:46:00 +08:00
更新统计信息后再试试
jtn007
2017-10-18 21:54:18 +08:00
自动优化,有可能用了索引一样也要全表扫描,所以就不使用了
sheldoner
2017-10-18 21:59:00 +08:00
哇,好炫酷的样子,请问是什么工具能看到执行时间?
sagaxu
2017-10-19 08:16:15 +08:00
rbo 和 cbo,现代 db 都是 cbo
dltsgl
2017-10-19 18:57:34 +08:00
全局索引改成本地索引就好了,不知道为啥

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/398722

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX