绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
为啥有索引也走全表扫描-十几年前得问题依然大量存在
2021-07-01 11:13:16

最近一个同事问为啥这个SQL怎么优化都是走全表扫描,让我帮着看看,从微信发来得txt执行计划看,第一眼我就预料是这个问题,隐式类型转换,函数屏蔽了索引,经过分析验证,最终确认是这个问题,下面我把问题仔细分析下,也再重温下hash-join和nested-loop得原理。

这是一个简单得SQL,从执行计划看两表HASH连接,hash连接时filter数据,具体SQL就不展示了

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1376627223

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 894 | 9957 (1) | 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 894 | 9957 (1 ) | 00:00:01 |
|* 2 | HASH JOIN | | 6 | 894 | 9956 (1) | 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED | mytab-1 | 1 | 59 | 4 (0) | 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_mytab-1_BOE_CODE | 1 | | 3 (0) | 00:00:01 |
|* 5 | TABLE ACCESS FULL | mytab-2 | 1400K| 120M| 9948 (1) | 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BS"."ID"=TO_NUMBER("HK"."BUSINESS_ID_"))
3 - filter("BS"."GROUP_ID"=:2)
4 - access("BS"."BOE_CODE"=:1)
5 - filter("HK"."ASSIGNEE_NAME_"<>'ACTION' OR "HK"."ASSIGNEE_NAME_" IS NULL)

其执行步骤是4-3-5-2-1-0,而4,3得结果集与5全表扫得结果集做hash jion,注意这个hash-join肯定有
过滤条件,否则就走笛卡尔积了,从谓词部分我们知道这个过滤条件是
access("BS"."ID"=TO_NUMBER("HK"."BUSINESS_ID_"))
也就是从这个字段做hash然后再做join,奇怪得是3,4的结果集仅仅返回一行数据,又在"HK"."BUSINESS_ID_"字段
做hash过滤数据,为啥不走嵌套循环呢,如果"HK"."BUSINESS_ID_"字段再表mytab-2中NDV很高,其实这里返回6行数据,说明适合通过索引解决
我们下面看下索引分布情况

TABLE_NAME INDEX_NAME COLUMN_NAME
------------------------------ ---------------------------------------- ------------------------------
mytab-1 IDX_mytab-1_BOE_CODE BOE_CODE
mytab-1 PK_mytab-1 ID
mytab-2 IDX_mytab-2_BUSINESS_ ID_ BUSINESS_ID_
mytab-2 SYS_C00411492 ID_

分析:表hk(mytab-2 )在 BUSINESS_ID_有个组合索引,我们理论分析它应该走跳跃扫描,然后通过嵌套执行在过滤数据,
但是原始执行计划用了hash,这里得问题就是为啥表从mytab-1中返回一条数据后,与表mytab-2关联为啥没走索引。
我们要注意下原始执行计划得第二步
2 - access("BS"."ID"=TO_NUMBER("HK"."BUSINESS_ID_"))
显然Oracle在字段"HK"."BUSINESS_ID_"加了函数TO_NUMBER,说明字段"BS"."ID"和"HK"."BUSINESS_ID_"得数据类型不一致
通过查询我们知道"BS"."ID"为number,而"HK"."BUSINESS_ID_"为varchar2,
问题找到了,由于表字段数据类型设计问题,导致了隐式类型转换,在有索引得字段加了函数导致索引失效。

解决方法:
创建函数索引

create indexuser1.IDX_mytab-2_NUMBER_BUSINESS_ID_ onuser1.mytab-2(TO_NUMBER("HK"."BUSINESS_ID_"));

创建函数索引后
SQL> l
1* SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY)
SQL> /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1195847964

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 924 | 13 (8)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 924 | 13 (8)| 00:00:01 |
| 2 | NESTED LOOPS | | 6 | 924 | 12 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED | mytab-1 | 1 | 59 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_mytab-1_BOE_CODE | 1 | | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED | mytab-2 | 6 | 570 | 8 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_mytab-2_NUMBER_BUSINESS_ID_ | 6 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("BS"."GROUP_ID"=:2)
4 - access("BS"."BOE_CODE"=:1)
5 - filter("HK"."ASSIGNEE_NAME_"<>'ACTION' OR "HK"."ASSIGNEE_NAME_" IS NULL)
6 - access("BS"."ID"=TO_NUMBER("BUSNESS_ID_"))

从执行计划看全表扫描没有了cost成本也下降了很多,(说明表mytab-2 不是小表),3,4步骤得结果集返回1行数据,这行数据传值
到"BS"."ID"=TO_NUMBER("BUSNESS_ID_")过滤条件,5,6步骤通过这个值通过索引找到6行数据,或者说因为只有6行数据,Oracle决定
使用索引,从而选择了嵌套循环得jion方式,从而优化了该SQL。


分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
猜你喜欢
戳我,来吐槽~