本帖最后由 flzhang 于 2018-3-19 20:06 编辑
这个sql如果用hint强制走索引本来可以很快,如果不用hint时,优化器就是不走索引一定要用全表扫描,
不用hint时,为什么优化器认为全表扫描的cost会最少呢?请大家帮忙看看
不加hint时的SQL 是这样的
SELECT T2.SERL_NO
,T1.SI_YMD
,T1.ST_YMD
,T1.ST2_YMD
,T1.SO_YMD
,T1.ACTU_DT
FROM MCS_HQ_READ.RW_BI_FT_LT_BAK T1
,MCS_HQ_READ.UP_LOAD_SERL10 T2
WHERE T1.SERL_NO = T2.SERL_NO
数据库版本 11.2.0.3.0 - 64bit
UP_LOAD_SERL10 表中数据 30行 无索引
RW_BI_FT_LT_BAK 表中数据 1千多万行
有主键索引 PK_RW_BI_FT_LT (SI_YMD,SERL_NO)
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 8189 | |
| 1 | HASH JOIN | | 10 | 1000 | 8189 | 00:02:39 |
| 2 | TABLE ACCESS FULL | UP_LOAD_SERL10 | 29 | 464 | 3 | 00:00:01 |
| 3 | TABLE ACCESS FULL | RW_BI_FT_LT_BAK| 1347K | 55M | 8169 | 00:02:39 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("T1"."SERL_NO"="T2"."SERL_NO")
虽然索引只能走SKIP INDEX,但我还是用hint强制SQL 用上索引,于是加hint的SQL
SELECT /*+ INDEX_SS(T1 PK_RW_BI_FT_LT) */
T2.SERL_NO
,T1.SI_YMD
,T1.ST_YMD
,T1.ST2_YMD
,T1.SO_YMD
,T1.ACTU_DT
FROM MCS_HQ_READ.RW_BI_FT_LT_BAK T1
,MCS_HQ_READ.UP_LOAD_SERL10 T2
WHERE T1.SERL_NO = T2.SERL_NO
============
Plan Table
============
-------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 643 | |
| 1 | NESTED LOOPS | | 10 | 580 | 643 | 00:00:08 |
| 2 | TABLE ACCESS FULL | UP_LOAD_SERL10 | 29 | 464 | 2 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | RW_BI_FT_LT_BAK| 1 | 42 | 64 | 00:00:01 |
| 4 | INDEX SKIP SCAN | PK_RW_BI_FT_LT | 1 | | 63 | 00:00:01 |
-------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("T1"."SERL_NO"="T2"."SERL_NO")
4 - filter("T1"."SERL_NO"="T2"."SERL_NO")
只要加上hint sql走SKIP INDEX 查询就非常快,但为什么这里优化器就不能自动判断出走SKIP INDEX呢?难道是bug么?
谢谢!
2018-3-19 20:06 上传
点击文件名下载附件
34.42 KB, 下载次数: 15