SQL条件中“is null”谓词导致全表扫描问题优化

最新推荐文章于 2024-08-14 02:18:27 发布

HonorHe

最新推荐文章于 2024-08-14 02:18:27 发布

阅读量9.3k

点赞数

分类专栏： oracle 文章标签： oracle sql

本文链接：https://blog.csdn.net/waixin/article/details/9415017

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL条件中“is null”谓词导致全表扫描问题优化

摘要：SQL，索引，is null，优化

正文：
前段时间针对相关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)

在非唯一索引上：当查询结果可能会返回多行数据时，所以会使用索引范围扫描。

执行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 '' || BAE007 ||

6 '',

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_TIME

21 from V_WF_TODOLIST

22 where (OPERID = '114627' or

23 (OPERID is null and BAE006 like '441905%' and exists

24 (select 1

25 from FW_OPERATOR2RIGHT A, FW_RIGHT B

26 where A.RIGHTID = B.RIGHTID

27 and A.AAE100 = B.AAE100

28 and A.AUTHTYPE = '1'

29 and A.AAE100 = '1'

30 and B.ACTION_DEF_ID = V_WF_TODOLIST.ACTION_DEF_ID

31 and A.OPERID = '114627') and not exists

32 (select 1

33 from WF_WORK_ITEM

34 where BAE007 = V_WF_TODOLIST.BAE007

35 and STATE = '5'

36 and FILTER_OPR = OPERID

37 and ACTION_DEF_ID in

38 (select ACTION_DEF_ID

39 from WF_WORK_ITEM

40 where BAE007 = V_WF_TODOLIST.BAE007

41 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 | 1

PLAN_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_D

AND "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"."STA

7 - 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_WO

TO_NUMBER("LOCATE"("ACTION_DEF_ID","FUN_FW_X_ACTION_IDS"(:B2,:B3))

AND "BAE007"=:B4 AND "OPERID"='114627' AND "FILTER_OPR"='114627' A

0 FROM "FW_RIGHT" "B","FW_OPERATOR2RIGHT" "A" WHERE "A"."OPERID"='

AND "A"."AUTHTYPE"='1' AND "A"."AAE100"='1' AND "B"."ACTION_DEF_ID

24 - 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 version

94 rows selected