DB2 执行计划filter下两个节点的SQL优化
最近更新：2023-05-22 11:33:11
适用范围
1.SQL 语句的where条件含有not exists/not in 子查询。 2.执行计划中两表/多表关联处出现filter.(单表的filter 不在该优化方案范围。)
问题概述
1.对于where条件含有not exists/not in 子查询的SQL，很多时候优化器会采用filter做两表/多表关联关系，类似于以主表为驱动表nested noop 子查询中的表。 2.Filter与NESTED LOOPS差别在于，FILTER内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率，但是如果主表结果集比较大，重复值较少，则循环次数多，此时FILTER操作将是严重影响SQL性能。 3.我们可以采用将not exists/not in 改写为left join 加子查询表的主键/关联字段is null 的形式改写，改写后让优化器更灵活的选择合适的两表join的方式。以达到提升SQL性能的目的。 4.如本例通过改写优化器选择了hash join ，SQL性能也从0m6.171s提升到0m0.183s。 5.DB2对于子查询产生的filter会自动改写，如果不能自动改写则建议手动改写，详细见链接： FILTER operator （粗略测了一下DB2 10.5.7对于not exists/not in 里的子查询带有聚合函数时无法自动改写，其余可自动改写。）
详细过程
原始SQL
SELECT COUNT(1)
FROM WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT r
where r.actid ='cunkuanchouhaoli' and r.state =1 and r.codeget =1
and r.source ='cunkuan' and not exists(
select custid
from (
select custid
from WYHA.WYH_TEST_INFOR_TESTABCD_TESTINFOLIST
where actid='cunkuanchouhaoli' and status =1 )re
where re.custid =r.custno )and not exists(
select custid
from (
select custid
from WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST r1
where r1.actid='cunkuanchouhaoli' and r1.prizetype ='1' )re
where re.custid =r.custno )and not exists(
select code
from (
select code
from WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST r2
where r2.actid ='cunkuanchouhaoli' and r2.code !='' and
r2.code is not null and created >=(current date)and
(created < current date + 1 days))re
where re.code =r.code )and not exists(
select custid
from (
select custid
from WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST r2
where r2.actid ='cunkuanchouhaoli' and r2.code !='' and
r2.code is not null and created >=(current date)and
(created < current date + 1 days)
group by custid
having count(custid)>=2 )re
where re.custid =r.custno )and r.timeofgiving < current date and
not exists(
select custid
from (
select custid
from WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST r2
where r2.actid ='cunkuanchouhaoli' and r2.code !='' and
r2.code is not null and created >=(current date - 3
days)and (created < current date - 2 days))re
where re.custid =r.custno )and exists (
select custno
from (
select custno
from WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT m
where m.actid ='cunkuanchouhaoli' and m.state =1 and
m.codeget =1 and m.source ='cunkuan'
group by custno
having count(1)< 5 )mc
where mc.custno =r.custno )and exists (
select custno
from (
select custno
from WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT m
where m.actid ='cunkuanchouhaoli' and m.source ='cunkuan' and
m.timeofgiving < current date - 20 days )mc
where mc.custno =r.custno )
原始SQL的执行时间及执行计划
-- 原始SQL 的执行时间
1
-----------
1592
1 record(s) selected.
real 0m6.171s
user 0m0.008s
sys 0m0.012s
-- 原始SQL的执行计划
Section Code Page = 1208
Estimated Cost = 8901.729492
Estimated Cardinality = 1.000000
Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_TESTINFOLIST ID = 2,663
| Index Scan: Name = SYSIBM.SQL230209191544000 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID (Ascending)
| #Columns = 1
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| Evaluate Predicates Before Locking for Key
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Data Prefetch: Sequential(1), Readahead
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 2
| | Process Build Table for Hash Join
Anti Left Outer Hash Join
| Early Out: Single Match Per Outer Row
| Estimated Build Size: 0
| Estimated Probe Size: 4000
| Access Table Name = WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST ID = 2,541
| | Index Scan: Name = WYHA.IND_WYH_TEST_WINGIFT_TEST_INFOMATION_TEST_CREATED ID = 2
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: CREATED (Descending)
| | #Columns = 0
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | #Key Columns = 1
| | | Start Key: Exclusive Value
| | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | 1: ?
| | Index-Only Access
| | Index Prefetch: Sequential(1), Readahead
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | Insert Into Integer Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 920.000000
| | | | | Row Width = 20
| | | | Duplicate Elimination
| Integer Sorted Temp Table Completion ID = t1
| List Prefetch Preparation
| | Access Table Name = WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST ID = 2,541
| | | #Columns = 1
| | | Skip Inserted Rows
| | | Avoid Locking Committed Data
| | | Currently Committed for Cursor Stability
| | | RID List Fetch Scan
| | | Fetch Using Prefetched List
| | | | Prefetch: 9 Pages
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 5
| | | | Process Build Table for Hash Join
| Anti Left Outer Hash Join
| | Early Out: Single Match Per Outer Row
| | Estimated Build Size: 4000
| | Estimated Probe Size: 4000
| | Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT ID = 2,547
| | | Index Scan: Name = WYHA.IND_WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT_TOG ID = 5
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: TIMEOFGIVING (Ascending)
| | | #Columns = 0
| | | Skip Inserted Rows
| | | Avoid Locking Committed Data
| | | Currently Committed for Cursor Stability
| | | #Key Columns = 1
| | | | Start Key: Beginning of Index
| | | | Stop Key: Exclusive Value
| | | | | 1: ?
| | | Index-Only Access
| | | Index Prefetch: Sequential(3), Readahead
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Index Predicate(s)
| | | | Insert Into Integer Sorted Temp Table ID = t2
| | | | | #Columns = 1
| | | | | #Sort Key Columns = 1
| | | | | | Key 1: (Ascending)
| | | | | Sortheap Allocation Parameters:
| | | | | | #Rows = 4843.000000
| | | | | | Row Width = 20
| | | | | Duplicate Elimination
| | Integer Sorted Temp Table Completion ID = t2
| | List Prefetch Preparation
| | | Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT ID = 2,547
| | | | #Columns = 3
| | | | Skip Inserted Rows
| | | | Avoid Locking Committed Data
| | | | Currently Committed for Cursor Stability
| | | | RID List Fetch Scan
| | | | Fetch Using Prefetched List
| | | | | Prefetch: 38 Pages
| | | | Lock Intents
| | | | | Table: Intent Share
| | | | | Row : Next Key Share
| | | | Sargable Predicate(s)
| | | | | #Predicates = 3
| | | | | Process Build Table for Hash Join
| | Hash Join
| | | Early Out: Single Match Per Inner Row
| | | Estimated Build Size: 104000
| | | Estimated Probe Size: 184000
| | | Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT ID = 2,547
| | | | Index Scan: Name = WYHA.IDX_WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT_SSCT ID = 7
| | | | | Regular Index (Not Clustered)
| | | | | Index Columns:
| | | | | | 1: SOURCE (Ascending)
| | | | | | 2: STATE (Ascending)
| | | | | | 3: CODEGET (Ascending)
| | | | | | 4: TIMEOFGIVING (Ascending)
| | | | #Columns = 0
| | | | Skip Inserted Rows
| | | | Avoid Locking Committed Data
| | | | Currently Committed for Cursor Stability
| | | | #Key Columns = 3
| | | | | Start Key: Inclusive Value
| | | | | | 1: 'cunkuan'
| | | | | | 2: 1
| | | | | | 3: 1
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 'cunkuan'
| | | | | | 2: 1
| | | | | | 3: 1
| | | | Index-Only Access
| | | | Index Prefetch: Sequential(36), Readahead
| | | | Isolation Level: Uncommitted Read
| | | | Lock Intents
| | | | | Table: Intent None
| | | | | Row : None
| | | | Sargable Index Predicate(s)
| | | | | Insert Into Integer Sorted Temp Table ID = t3
| | | | | | #Columns = 1
| | | | | | #Sort Key Columns = 1
| | | | | | | Key 1: (Ascending)
| | | | | | Sortheap Allocation Parameters:
| | | | | | | #Rows = 22819.000000
| | | | | | | Row Width = 20
| | | | | | Duplicate Elimination
| | | Integer Sorted Temp Table Completion ID = t3
| | | List Prefetch Preparation
| | | | Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT ID = 2,547
| | | | | #Columns = 1
| | | | | Skip Inserted Rows
| | | | | Avoid Locking Committed Data
| | | | | Currently Committed for Cursor Stability
| | | | | RID List Fetch Scan
| | | | | Fetch Using Prefetched List
| | | | | | Prefetch: 201 Pages
| | | | | Lock Intents
| | | | | | Table: Intent Share
| | | | | | Row : Next Key Share
| | | | | Sargable Predicate(s)
| | | | | | #Predicates = 4
| | | | | | Insert Into Sorted Temp Table ID = t4
| | | | | | | #Columns = 2
| | | | | | | #Sort Key Columns = 1
| | | | | | | | Key 1: CUSTNO (Ascending)
| | | | | | | Sortheap Allocation Parameters:
| | | | | | | | #Rows = 14821.000000
| | | | | | | | Row Width = 24
| | | | | | | Piped
| | | | | | | Partial Aggregation
| | | Sorted Temp Table Completion ID = t4
| | | Access Temp Table ID = t4
| | | | #Columns = 2
| | | | Relation Scan
| | | | | Prefetch: Eligible
| | | Final Aggregation
| | | | Group By
| | | | Column Function(s)
| | | Residual Predicate(s)
| | | | #Predicates = 1
| | Hash Join
| | | Early Out: Single Match Per Outer Row
| | | Estimated Build Size: 48000
| | | Estimated Probe Size: 64000
| | | Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT ID = 2,547
| | | | Index Scan: Name = WYHA.IDX_WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT_SSCT ID = 7
| | | | | Regular Index (Not Clustered)
| | | | | Index Columns:
| | | | | | 1: SOURCE (Ascending)
| | | | | | 2: STATE (Ascending)
| | | | | | 3: CODEGET (Ascending)
| | | | | | 4: TIMEOFGIVING (Ascending)
| | | | #Columns = 0
| | | | Skip Inserted Rows
| | | | Avoid Locking Committed Data
| | | | Currently Committed for Cursor Stability
| | | | #Key Columns = 4
| | | | | Start Key: Inclusive Value
| | | | | | 1: 'cunkuan'
| | | | | | 2: 1
| | | | | | 3: 1
| | | | | Stop Key: Exclusive Value
| | | | | | 1: 'cunkuan'
| | | | | | 2: 1
| | | | | | 3: 1
| | | | | | 4: ?
| | | | Index-Only Access
| | | | Index Prefetch: Sequential(1), Readahead
| | | | Isolation Level: Uncommitted Read
| | | | Lock Intents
| | | | | Table: Intent None
| | | | | Row : None
| | | | Sargable Index Predicate(s)
| | | | | Insert Into Integer Sorted Temp Table ID = t5
| | | | | | #Columns = 1
| | | | | | #Sort Key Columns = 1
| | | | | | | Key 1: (Ascending)
| | | | | | Sortheap Allocation Parameters:
| | | | | | | #Rows = 536.000000
| | | | | | | Row Width = 20
| | | | | | Duplicate Elimination
| | | Integer Sorted Temp Table Completion ID = t5
| | | List Prefetch Preparation
| | | | Access Table Name = WYHA.WYH_TEST_INFOR_TESTABCD_CODE_MANAGEMENT ID = 2,547
| | | | | #Columns = 4
| | | | | Skip Inserted Rows
| | | | | Avoid Locking Committed Data
| | | | | Currently Committed for Cursor Stability
| | | | | RID List Fetch Scan
| | | | | Fetch Using Prefetched List
| | | | | | Prefetch: 12 Pages
| | | | | Lock Intents
| | | | | | Table: Intent Share
| | | | | | Row : Next Key Share
| | | | | Sargable Predicate(s)
| | | | | | #Predicates = 5
| | | | | | Process Probe Table for Hash Join
| Anti Left Outer Nested Loop Join
| | Access Table Name = WYHA.WYH_TEST_WINGIFT_TEST_INFOMATION_TEST ID = 2,541
| | | Index Scan: Name = WYHA.IND_WYH_TEST_WINGIFT_TEST_INFOMATION_TEST_CREATED ID = 2
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: CREATED (Descending)
| | | #Columns = 0
| | | Single Record
| | | Skip Inserted Rows
| | | Avoid Locking Committed Data
| | | Currently Committed for Cursor Stability
| | | Evaluate Predicates Before Locking for Key
| | | #Key Columns = 1
| | | | Start Key: Exclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: Sequential(1), Readahead
| | | Index Prefetch: Sequential(1), Readahead
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
| | | Sargable Predicate(s)
| | | | #Predicates = 4
| Anti Left Outer Nested Loop Join......
登录MES账号 查看全文详情
登录/注册
