SQL条件中“is null”谓词导致全表扫描问题优化
摘要:SQL,索引,is null,优化
正文:
前段时间针对相关SQL进行了优化,零散记录了下优化过程,这次整理出来与大家分享。若有描述不当或补充的地方请留言,谢谢!
方法1:用固定值建复合索引优化
前段时间针对相关SQL进行了优化,零散记录了下优化过程,这次整理出来与大家分享。若有描述不当或补充的地方请留言,谢谢!
方法1:用固定值建复合索引优化
1)新建测试表
create table hr_tab1(id number(10) not null,name varchar2(20) );
2)未建索引之前执行计划
SQL>explain plan for select * from hr_tab1 where name is null;
Plan hash value:
2713387046
-----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| HR_TAB1 | 1 | 25 | 2 (0)| 00:00:01 |-----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("NAME" IS NULL)Note------ 'PLAN_TABLE' is old version- dynamic sampling used for this statement
3)使用空值列name跟一个固定值建复合索引
建复合索引:
create index IND_HR_TAB1_HH1 on hr_tab1 (name,1)
4)查看优化后执行计划
执行SQL:
SQL>
explain plan for select * from hr_tab1 where name is null;
Plan hash value:
538103671
-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 25 | 0 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| HR_TAB1 | 1 | 25 | 0 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IND_HR_TAB1_HH1 | 1 | | 0 (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("NAME" IS NULL)Note------ 'PLAN_TABLE' is old version- dynamic sampling used for this statement (level=2)
从执行计划上看,建了复合索引之后,在查询is null时走了范围扫描索引。
在这里有必要说明一下为什么会走索引范围扫描。索引范围扫描主要出现在以下些情况:
在唯一索引上:
在唯一索引上
使用一个索引存取多行数据,
这种情况索引范围扫描的典型情况是在谓词(where限制条件)中使用了范围 操作符 (如>、<、<>、>=、<=、between)
这种情况索引范围扫描的典型情况是在谓词(where限制条件)中使用了范围 操作符 (如>、<、<>、>=、<=、between)
在非唯一索引上:当查询结果可能会返回多行数据时,所以会使用索引范围扫描。
执行SQL:
explain plan for select name from hr_tab1 where name is null;
Plan hash value: 3977038940------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IND_HR_TAB1_HH1 | 1 | 12 | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - access("NAME" IS NULL)Note------ 'PLAN_TABLE' is old version- dynamic sampling used for this statement (level=2)
方法二:通过函数索引优化
以下是流程待办任务查询SQL,涉及到权限和流程互斥,所以这个SQL比较复杂。
查看优化前执行计划:
SQL> explain plan for select DECODE(NVL(OPERID, '0'), '0', '岗位待办', '个人待办') TASK,2 PROCESS_DEF_NAME,3 DECODE(IS_RETURN,4 '1',5 '<span style="color:red">' || BAE007 ||6 '</span>',7 BAE007) BAE007,8 ACTION_DEF_NAME,9 PROCESS_KEY_INFO,10 '<a href=Workflow.do?pid=' || BAE007 || 'wid=' ||11 WORK_ITEM_ID || '>办理</a>' BL,12 DECODE(NVL(OPERID, '0'),13 '0',14 '<a href="javascript:jieShou(''' || WORK_ITEM_ID ||15 ''')">接收</a>',16 '<a href="javascript:cheXiao(''' || WORK_ITEM_ID ||17 ''')">撤销</a>') DO,18 '<a href="javascript:detail(''' || BAE007 ||19 ''')">经办详情</a>' DETAIL,20 BSTART_TIME21 from V_WF_TODOLIST22 where (OPERID = '114627' or23 (OPERID is null and BAE006 like '441905%' and exists24 (select 125 from FW_OPERATOR2RIGHT A, FW_RIGHT B26 where A.RIGHTID = B.RIGHTID27 and A.AAE100 = B.AAE10028 and A.AUTHTYPE = '1'29 and A.AAE100 = '1'30 and B.ACTION_DEF_ID = V_WF_TODOLIST.ACTION_DEF_ID31 and A.OPERID = '114627') and not exists32 (select 133 from WF_WORK_ITEM34 where BAE007 = V_WF_TODOLIST.BAE00735 and STATE = '5'36 and FILTER_OPR = OPERID37 and ACTION_DEF_ID in38 (select ACTION_DEF_ID39 from WF_WORK_ITEM40 where BAE007 = V_WF_TODOLIST.BAE00741 and LOCATE(ACTION_DEF_ID,42 V_WF_TODOLIST.X_ACTION_DEF_IDS) > 0)43 and OPERID = '114627')))44 order by TASK desc, BSTART_TIME desc, BAE007 desc;Explained查看计划描述:SQL> select * from table (dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 4153392385--------------------------------------------------------------------------------| Id | Operation | Name | Row--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1| 1 | SORT ORDER BY | | 1| 2 | CONCATENATION | ||* 3 | FILTER | ||* 4 | HASH JOIN | | 1|* 5 | TABLE ACCESS FULL | WF_WORK_ITEM | 1| 6 | TABLE ACCESS FULL | WF_PROCESS_INSTANCE | 675|* 7 | TABLE ACCESS BY INDEX ROWID | FW_OPERATOR2RIGHT || 8 | NESTED LOOPS | ||* 9 | TABLE ACCESS BY INDEX ROWID | FW_RIGHT ||* 10 | INDEX RANGE SCAN | IND_FW_RIGHT_ACTION_DEF_ID || 11 | BITMAP CONVERSION TO ROWIDS | || 12 | BITMAP AND | || 13 | BITMAP CONVERSION FROM ROWIDS | ||* 14 | INDEX RANGE SCAN | IND_OPERATOR2RIGHT_OPERID | 1PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------| 15 | BITMAP CONVERSION FROM ROWIDS | ||* 16 | INDEX RANGE SCAN | IND_OPERATOR2RIGHT_RIGHTID | 1|* 17 | TABLE ACCESS BY INDEX ROWID | WF_WORK_ITEM || 18 | NESTED LOOPS | ||* 19 | TABLE ACCESS BY INDEX ROWID | WF_WORK_ITEM ||* 20 | INDEX RANGE SCAN | IND_WF_WORK_ITEM ||* 21 | INDEX RANGE SCAN | IND_WF_WORK_ITEM ||* 22 | FILTER | || 23 | NESTED LOOPS | ||* 24 | TABLE ACCESS BY INDEX ROWID | WF_WORK_ITEM ||* 25 | INDEX RANGE SCAN | IND_WF_WORK_ITEM_OPERID || 26 | TABLE ACCESS BY INDEX ROWID | WF_PROCESS_INSTANCE ||* 27 | INDEX UNIQUE SCAN | PK_WF_PROCESS_INSTANCE ||* 28 | TABLE ACCESS BY INDEX ROWID | WF_WORK_ITEM || 29 | NESTED LOOPS | ||* 30 | TABLE ACCESS BY INDEX ROWID | WF_WORK_ITEM ||* 31 | INDEX RANGE SCAN | IND_WF_WORK_ITEM ||* 32 | INDEX RANGE SCAN | IND_WF_WORK_ITEM ||* 33 | TABLE ACCESS BY INDEX ROWID | FW_OPERATOR2RIGHT || 34 | NESTED LOOPS | ||* 35 | TABLE ACCESS BY INDEX ROWID | FW_RIGHT |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------|* 36 | INDEX RANGE SCAN | IND_FW_RIGHT_ACTION_DEF_ID || 37 | BITMAP CONVERSION TO ROWIDS | || 38 | BITMAP AND | || 39 | BITMAP CONVERSION FROM ROWIDS| ||* 40 | INDEX RANGE SCAN | IND_OPERATOR2RIGHT_OPERID | 1| 41 | BITMAP CONVERSION FROM ROWIDS| ||* 42 | INDEX RANGE SCAN | IND_OPERATOR2RIGHT_RIGHTID | 1--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter( EXISTS (SELECT 0 FROM "FW_RIGHT" "B","FW_OPERATOR2RIGHT" "A" WHER"A"."RIGHTID"="B"."RIGHTID" AND "A"."AUTHTYPE"='1' AND "A"."AAE100"B"."AAE100"='1') AND NOT EXISTS (SELECT 0 FROM "WF_WORK_ITEM" "W"WF_WORK_ITEM" WHERE "BAE007"=:B2 AND TO_NUMBER("LOCATE"("ACTION_DAND "ACTION_DEF_ID"="ACTION_DEF_ID" AND "BAE007"=:B5 AND "OPERID"="STATE"='5'))4 - access("B"."BAE007"="A"."BAE007")5 - filter("A"."OPERID" IS NULL AND "A"."BAE006" LIKE '441905%' AND ("A"."STA7 - filter("A"."AUTHTYPE"='1' AND "A"."AAE100"='1')PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------9 - filter("B"."AAE100"='1')10 - access("B"."ACTION_DEF_ID"=:B1)14 - access("A"."OPERID"='114627')16 - access("A"."RIGHTID"="B"."RIGHTID")17 - filter(TO_NUMBER("LOCATE"("ACTION_DEF_ID","FUN_FW_X_ACTION_IDS"(:B1,:B2))"ACTION_DEF_ID"="ACTION_DEF_ID")19 - filter("OPERID"='114627' AND "FILTER_OPR"='114627' AND "STATE"='5')20 - access("BAE007"=:B1)21 - access("BAE007"=:B1)22 - filter(LNNVL("A"."OPERID" IS NULL) OR LNNVL("A"."BAE006" LIKE '441905%')(SELECT 0 FROM "WF_WORK_ITEM" "WF_WORK_ITEM","WF_WORK_ITEM" "WF_WOTO_NUMBER("LOCATE"("ACTION_DEF_ID","FUN_FW_X_ACTION_IDS"(:B2,:B3))AND "BAE007"=:B4 AND "OPERID"='114627' AND "FILTER_OPR"='114627' A0 FROM "FW_RIGHT" "B","FW_OPERATOR2RIGHT" "A" WHERE "A"."OPERID"='AND "A"."AUTHTYPE"='1' AND "A"."AAE100"='1' AND "B"."ACTION_DEF_ID24 - filter("A"."STATE"='0' OR "A"."STATE"='2')25 - access("A"."OPERID"='114627')27 - access("B"."BAE007"="A"."BAE007")28 - filter(TO_NUMBER("LOCATE"("ACTION_DEF_ID","FUN_FW_X_ACTION_IDS"(:B1,:B2))"ACTION_DEF_ID"="ACTION_DEF_ID")30 - filter("OPERID"='114627' AND "FILTER_OPR"='114627' AND "STATE"='5')PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------31 - access("BAE007"=:B1)32 - access("BAE007"=:B1)33 - filter("A"."AUTHTYPE"='1' AND "A"."AAE100"='1')35 - filter("B"."AAE100"='1')36 - access("B"."ACTION_DEF_ID"=:B1)40 - access("A"."OPERID"='114627')42 - access("A"."RIGHTID"="B"."RIGHTID")Note------ 'PLAN_TABLE' is old version94 rows selected
执行计划显示,索引列OPERID 在使用OPERID is null条件查时导致全表扫描了。
优化:
增加函数索引,并修改语句将or 改成union all。
create index IND_WF_WORK_ITEM_HS1 on wf_work_item(nvl(operid,'1'));
优化后SQL语句为:
select DECODE(NVL(OPERID, '0'), '0', '岗位待办', '个人待办') TASK,
PROCESS_DEF_NAME,
DECODE(IS_RETURN,
'1',
'<span style="color:red">' || BAE007 ||
'</span>',
BAE007) BAE007,
ACTION_DEF_NAME,
PROCESS_KEY_INFO,
'<a href=Workflow.do?pid=' || BAE007 || 'wid=' ||
WORK_ITEM_ID || '>办理</a>' BL,
DECODE(NVL(OPERID, '0'),
'0',
'<a href="javascript:jieShou(''' || WORK_ITEM_ID ||
''')">接收</a>',
'<a href="javascript:cheXiao(''' || WORK_ITEM_ID ||
''')">撤销</a>') DO,
'<a href="javascript:detail(''' || BAE007 ||
''')">经办详情</a>' DETAIL,
BSTART_TIME
from V_WF_TODOLIST
where OPERID = '114627'
union all
select DECODE(NVL(OPERID, '0'), '0', '岗位待办', '个人待办') TASK,
PROCESS_DEF_NAME,
DECODE(IS_RETURN,
'1',
'<span style="color:red">' || BAE007 ||
'</span>',
BAE007) BAE007,
ACTION_DEF_NAME,
PROCESS_KEY_INFO,
'<a href=Workflow.do?pid=' || BAE007 || 'wid=' ||
WORK_ITEM_ID || '>办理</a>' BL,
DECODE(NVL(OPERID, '0'),
'0',
'<a href="javascript:jieShou(''' || WORK_ITEM_ID ||
''')">接收</a>',
'<a href="javascript:cheXiao(''' || WORK_ITEM_ID ||
''')">撤销</a>') DO,
'<a href="javascript:detail(''' || BAE007 ||
''')">经办详情</a>' DETAIL,
BSTART_TIME
from V_WF_TODOLIST
where exists
(select 1
from FW_OPERATOR2RIGHT A, FW_RIGHT B
where A.RIGHTID = B.RIGHTID
and A.AAE100 = B.AAE100
and A.AUTHTYPE = '1'
and A.AAE100 = '1'
and B.ACTION_DEF_ID = V_WF_TODOLIST.ACTION_DEF_ID
and A.OPERID = '114627')
and not exists
(select 1
from WF_WORK_ITEM
where BAE007 = V_WF_TODOLIST.BAE007
and STATE = '5'
and FILTER_OPR = OPERID
and ACTION_DEF_ID in
(select ACTION_DEF_ID
from WF_WORK_ITEM
where BAE007 = V_WF_TODOLIST.BAE007
and LOCATE(ACTION_DEF_ID,
V_WF_TODOLIST.X_ACTION_DEF_IDS) > 0)
and OPERID = '114627')
and nvl(OPERID,'1') = '1' and BAE006 like '441905%'
order by TASK desc, BSTART_TIME desc, BAE007 desc
经过以上优化后,不再有全表扫描了。