提问人：Dani Che 提问时间：10/13/2023 更新时间：10/13/2023 访问量：64
使用 EXISTS 时出现奇怪的性能问题
Weird performance problem when using EXISTS
问：
我通过将 LEFT JOIN 和 OR 替换为 EXISTING 来改进我的 SELECT：
问题是原始 SELECT 需要 700 毫秒，但新更改的 SELECT 需要 35+ 分钟才能执行（并且仍在计数）。这太疯狂了。有人对此有任何解释吗？如何找到导致此性能问题的原因？它看起来像某种永无止境的循环......？
旧 SELECT：
SELECT pcl.COL1,
pcl.COL2,
pcl.COL3,
pcl.COL4,
(SELECT cc.COL5
FROM T3 cc
JOIN T4 cvp
ON cvp.COL10 = cc.COL10
AND cvp.COL2 = pcl.COL2) AS COL10,
pp.COL11
FROM T1 ba
JOIN T2 pcl
ON pcl.COL1 = ba.COL1
AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
ON pscl.COL2 = pcl.COL2
AND pscl.COL4 <> 3
AND (pscl.COL6 = ba.COL6 OR ba.COL6 = 'ALL')
LEFT JOIN T6 bba
ON bba.COL7 = ba.COL7
LEFT JOIN T7 bsa
ON bsa.COL7 = ba.COL7
LEFT JOIN T8 pp
ON pp.COL1 = pcl.COL1
WHERE (ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)
AND ba.COL4 <> 3
AND pcl.COL4 <> 3
AND pscl.COL4 <> 3
AND NOT EXISTS
(SELECT *
FROM T1 ba2
WHERE ba2.COL1 = ba.COL1
AND ((ba2.COL2 = pcl.COL2 AND
(ba2.COL6 = pscl.COL6 OR ba2.COL6 = 'ALL')) OR
(ba.COL6 = 'ALL' AND ba.COL2 = 0 AND ba2.COL6 = pscl.COL6))
AND (ba2.COL2 <> ba.COL2 OR ba2.COL2 = 0)
AND ba2.COL13 = ba.COL13
AND ba2.COL12 = ba.COL12
AND ba2.COL4 <> 3)
UNION (SELECT pcl.COL1,
pcl.COL2,
pcl.COL3,
pcl.COL4,
(SELECT cc.COL5
FROM T3 cc
JOIN T4 cvp
ON cvp.COL10 = cc.COL10
AND cvp.COL2 = pcl.COL2) AS COL10,
pp.COL11
FROM T9 sa
LEFT JOIN T2 pcl
on sa.COL1 = pcl.COL1
and (sa.COL2 = pcl.COL2 or sa.COL2 = 0)
and (sa.COL14 = pcl.COL14)
and pcl.COL4 <> 6
and pcl.COL4 is not null
AND pcl.COL3 is not null
LEFT JOIN T8 pp
on pp.COL1 = pcl.COL1
WHERE sa.COL8 = 617617
AND pcl.COL14 = 100805)
ORDER BY COL11, COL1, COL4, COL3;
新 SELECT：
SELECT pcl.COL1,
pcl.COL2,
pcl.COL3,
pcl.COL4,
(SELECT cc.COL5
FROM T3 cc
JOIN T4 cvp
ON cvp.COL10 = cc.COL10
AND cvp.COL2 = pcl.COL2) AS COL10,
pp.COL11
FROM T1 ba
JOIN T2 pcl
ON pcl.COL1 = ba.COL1
AND (pcl.COL2 = ba.COL2 OR ba.COL2 = 0)
JOIN T5 pscl
ON pscl.COL2 = pcl.COL2
AND pscl.COL4 <> 3
AND (pscl.COL6 = ba.COL6 OR
ba.COL6 = 'ALL')
LEFT JOIN T8 pp
ON pp.COL1 = pcl.COL1
WHERE ba.COL8 = 617617
OR EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617)
OR EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617)
AND ba.COL4 <> 3
AND pcl.COL4 <> 3
AND pscl.COL4 <> 3
AND NOT EXISTS
(SELECT *
FROM T1 ba2
WHERE ba2.COL1 = ba.COL1
AND ((ba2.COL2 = pcl.COL2 AND
(ba2.COL6 = pscl.COL6 OR
ba2.COL6 = 'ALL')) OR
(ba.COL6 = 'ALL' AND
ba.COL2 = 0 AND
ba2.COL6 = pscl.COL6))
AND (ba2.COL2 <> ba.COL2 OR
ba2.COL2 = 0)
AND ba2.COL13 = ba.COL13
AND ba2.COL12 = ba.COL12
AND ba2.COL4 <> 3
)
UNION (SELECT pcl.COL1,
pcl.COL2,
pcl.COL3,
pcl.COL4,
(SELECT cc.COL5
FROM T3 cc
JOIN T4 cvp
ON cvp.COL10 = cc.COL10
AND cvp.COL2 = pcl.COL2) AS COL10,
pp.COL11
FROM T9 sa
LEFT JOIN T2 pcl
on sa.COL1 = pcl.COL1
and (sa.COL2 = pcl.COL2 or
sa.COL2 = 0)
and (sa.COL14 = pcl.COL14)
and pcl.COL4 <> 6
and pcl.COL4 is not null
AND pcl.COL3 is not null
LEFT JOIN T8 pp
on pp.COL1 = pcl.COL1
WHERE sa.COL8 = 617617
AND pcl.COL14 = 100805)
ORDER BY COL11, COL1, COL4, COL3;
旧执行计划：
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.89 | 64899 | | | |
| 1 | SORT UNIQUE | | 1 | 4136K| 1 |00:00:00.89 | 64899 | 2048 | 2048 | 1/0/0|
| 2 | UNION-ALL | | 1 | | 27 |00:00:00.71 | 64899 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 1 | 1 |00:00:00.01 | 5 | | | |
|* 5 | INDEX UNIQUE SCAN | I_PK_VPCCONT | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 7 | INDEX UNIQUE SCAN | I_PK_SYSCONCA | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 8 | FILTER | | 1 | | 27 |00:00:00.71 | 64888 | | | |
| 9 | NESTED LOOPS | | 1 | 5940K| 27 |00:00:00.71 | 64885 | | | |
| 10 | NESTED LOOPS | | 1 | 5940K| 81 |00:00:00.71 | 64861 | | | |
| 11 | NESTED LOOPS OUTER | | 1 | 7702 | 1 |00:00:00.89 | 64857 | | | |
|* 12 | HASH JOIN | | 1 | 7702 | 1 |00:00:00.89 | 64853 | 962K| 962K| 1/0/0|
|* 13 | FILTER | | 1 | | 1 |00:00:00.25 | 38012 | | | |
|* 14 | HASH JOIN RIGHT OUTER | | 1 | 16426 | 1645K|00:00:00.84 | 38012 | 2920K| 2920K| 1/0/0|
| 15 | VIEW | index$_join$_011 | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 16 | HASH JOIN | | 1 | | 0 |00:00:00.01 | 4 | 2078K| 2078K| 1/0/0|
| 17 | INDEX FAST FULL SCAN | I_FK_BUSTLACC_SETTACC_COL8 | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
| 18 | INDEX FAST FULL SCAN | I_UK_BUSTLACC_COL7_AMOUNTTYPE | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 19 | HASH JOIN RIGHT OUTER | | 1 | 1641K| 1645K|00:00:00.54 | 38008 | 24M| 4789K| 1/0/0|
| 20 | TABLE ACCESS FULL | T6 | 1 | 414K| 413K|00:00:00.01 | 1634 | | | |
|* 21 | TABLE ACCESS FULL | T1 | 1 | 1632K| 1636K|00:00:00.28 | 36374 | | | |
|* 22 | TABLE ACCESS FULL | T2 | 1 | 1100K| 1102K|00:00:00.16 | 26841 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | T8 | 1 | 1 | 1 |00:00:00.01 | 4 | | | |
|* 24 | INDEX UNIQUE SCAN | I_PK_PARTNER | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 25 | INDEX RANGE SCAN | I_FK_PCSUBLIN_CONTRACT_COL2 | 1 | 8 | 81 |00:00:00.01 | 4 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | T5 | 81 | 771 | 27 |00:00:00.01 | 24 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
|* 28 | INDEX RANGE SCAN | I_BUSIACC_COL1_3C | 1 | 1 | 0 |00:00:00.01 | 3 | | | |
| 29 | NESTED LOOPS | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 30 | TABLE ACCESS BY INDEX ROWID | T4 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 31 | INDEX UNIQUE SCAN | I_PK_VPCCONT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | T3 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 33 | INDEX UNIQUE SCAN | I_PK_SYSCONCA | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 34 | NESTED LOOPS OUTER | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
| 35 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 36 | TABLE ACCESS BY INDEX ROWID | T9 | 1 | 1 | 0 |00:00:00.01 | 4 | | | |
|* 37 | INDEX UNIQUE SCAN | I_PK_SETTACC | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
|* 38 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 39 | INDEX RANGE SCAN | I_PARCONLI_PID_PARTNERMCC | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 40 | TABLE ACCESS BY INDEX ROWID | T8 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 41 | INDEX UNIQUE SCAN | I_PK_PARTNER | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CVP"."COL2"=:B1)
7 - access("CVP"."COL10"="CC"."COL10")
8 - filter( IS NULL)
12 - access("PCL"."COL1"="BA"."COL1")
filter(("PCL"."COL2"="BA"."COL2" OR "BA"."COL2"=0))
13 - filter(("BA"."COL8"=617617 OR "BSA"."COL8"=617617 OR "BBA"."COL9"=617617))
14 - access("BSA"."COL7"="BA"."COL7")
16 - access(ROWID=ROWID)
19 - access("BBA"."COL7"="BA"."COL7")
21 - filter("BA"."COL4"<>3)
22 - filter("PCL"."COL4"<>3)
24 - access("PP"."COL1"="PCL"."COL1")
25 - access("PSCL"."COL2"="PCL"."COL2")
26 - filter(("PSCL"."COL4"<>3 AND ("PSCL"."COL6"="BA"."COL6" OR "BA"."COL6"='ALL')))
27 - filter(("BA2"."COL12"=:B1 AND "BA2"."COL13"=:B2 AND "BA2"."COL4"<>3))
28 - access("BA2"."COL1"=:B1)
filter((((:B1='ALL' AND :B2=0 AND "BA2"."COL6"=:B3) OR ("BA2"."COL2"=:B4 AND INTERNAL_FUNCTION("BA2"."COL6"))) AND
("BA2"."COL2"<>:B5 OR "BA2"."COL2"=0)))
31 - access("CVP"."COL2"=:B1)
33 - access("CVP"."COL10"="CC"."COL10")
36 - filter("SA"."COL14"=100805)
37 - access("SA"."COL8"=617617)
38 - filter(("PCL"."COL14"=100805 AND ("SA"."COL2"="PCL"."COL2" OR "SA"."COL2"=0) AND "PCL"."COL4"<>6))
39 - access("SA"."COL1"="PCL"."COL1")
41 - access("PP"."COL1"="PCL"."COL1")
新的执行计划：
Plan hash value: 3990748374
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50M| 4884M| | 2162K (1)| 00:01:25 |
| 1 | SORT UNIQUE | | 50M| 4884M| 10G| 1268K (1)| 00:00:50 |
| 2 | UNION-ALL | | | | | | |
| 3 | NESTED LOOPS | | 1 | 28 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 9 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_PK_VPCCONT | 1 | | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 19 | | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_PK_SYSCONCA | 1 | | | 1 (0)| 00:00:01 |
|* 8 | FILTER | | | | | | |
|* 9 | HASH JOIN | | 979M| 93G| 119M| 98927 (8)| 00:00:04 |
|* 10 | HASH JOIN RIGHT OUTER | | 1269K| 105M| 49M| 48138 (1)| 00:00:02 |
| 11 | TABLE ACCESS FULL | T8 | 1353K| 33M| | 16120 (1)| 00:00:01 |
|* 12 | HASH JOIN | | 1269K| 73M| 52M| 25182 (1)| 00:00:01 |
| 13 | TABLE ACCESS FULL | T2 | 1488K| 35M| | 7510 (1)| 00:00:01 |
| 14 | TABLE ACCESS FULL | T1 | 2232K| 76M| | 9964 (1)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | T5 | 5413K| 77M| | 30731 (1)| 00:00:02 |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED | T6 | 1 | 12 | | 1 (0)| 00:00:01 |
|* 17 | INDEX SKIP SCAN | I_UK_BUBIACC_BUSIACCNO_CONDCOD | 1 | | | 1 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED | T7 | 1 | 26 | | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_FK_BUSTLACC_SETTACC_COL8 | 1 | | | 1 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID BATCHED | T1 | 1 | 24 | | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_BUSIACC_COL1_3C | 1 | | | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 28 | | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | T4 | 1 | 9 | | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_PK_VPCCONT | 1 | | | 1 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | T3 | 1 | 19 | | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_PK_SYSCONCA | 1 | | | 1 (0)| 00:00:01 |
| 27 | NESTED LOOPS OUTER | | 1 | 76 | | 3 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | 1 | 50 | | 2 (0)| 00:00:01 |
|* 29 | TABLE ACCESS BY INDEX ROWID | T9 | 1 | 20 | | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | I_PK_SETTACC | 1 | | | 1 (0)| 00:00:01 |
|* 31 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 30 | | 1 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | I_PARCONLI_PID_PARTNERMCC | 1 | | | 1 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | T8 | 1 | 26 | | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | I_PK_PARTNER | 1 | | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CVP"."COL2"=:B1)
7 - access("CVP"."COL10"="CC"."COL10")
8 - filter("BA"."COL8"=617617 OR EXISTS (SELECT 0 FROM "IPASS_PROD"."T6" "BBA" WHERE
"BBA"."COL7"=:B1 AND "BBA"."COL9"=617617) OR "BA"."COL4"<>3 AND "PCL"."COL4"<>3 AND
EXISTS (SELECT 0 FROM "IPASS_PROD"."T7" "BSA" WHERE "BSA"."COL8"=617617 AND
"BSA"."COL7"=:B2) AND NOT EXISTS (SELECT 0 FROM "IPASS_PROD"."T1" "BA2" WHERE "BA2"."COL1"=:B3 AND
"BA2"."COL12"=:B4 AND "BA2"."COL13"=:B5 AND "BA2"."COL4"<>3 AND (:B6='ALL' AND :B7=0 AND "BA2"."COL6"=:B8 OR
"BA2"."COL2"=:B9 AND ("BA2"."COL6"=:B10 OR "BA2"."COL6"='ALL')) AND ("BA2"."COL2"<>:B11 OR
"BA2"."COL2"=0)))
9 - access("PSCL"."COL2"="PCL"."COL2")
filter("PSCL"."COL6"="BA"."COL6" OR "BA"."COL6"='ALL')
10 - access("PP"."COL1"(+)="PCL"."COL1")
12 - access("PCL"."COL1"="BA"."COL1")
filter("PCL"."COL2"="BA"."COL2" OR "BA"."COL2"=0)
15 - filter("PSCL"."COL4"<>3)
16 - filter("BBA"."COL9"=617617)
17 - access("BBA"."COL7"=:B1)
18 - filter("BSA"."COL7"=:B1)
19 - access("BSA"."COL8"=617617)
20 - filter("BA2"."COL12"=:B1 AND "BA2"."COL13"=:B2 AND "BA2"."COL4"<>3)
21 - access("BA2"."COL1"=:B1)
filter((:B1='ALL' AND :B2=0 AND "BA2"."COL6"=:B3 OR "BA2"."COL2"=:B4 AND ("BA2"."COL6"=:B5 OR
"BA2"."COL6"='ALL')) AND ("BA2"."COL2"<>:B6 OR "BA2"."COL2"=0))
24 - access("CVP"."COL2"=:B1)
26 - access("CVP"."COL10"="CC"."COL10")
29 - filter("SA"."COL14"=100805)
30 - access("SA"."COL8"=617617)
31 - filter("PCL"."COL14"=100805 AND ("SA"."COL2"="PCL"."COL2" OR "SA"."COL2"=0) AND
"PCL"."COL4"<>6)
32 - access("SA"."COL1"="PCL"."COL1")
34 - access("PP"."COL1"(+)="PCL"."COL1")
答：
4赞
Alex Poole
10/13/2023
#1
在回答您之前的问题时，查询更简单，整个子句是：where
WHERE ba.COL8 = 617617
OR EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617)
OR EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617)
您现在已添加更多条件：
WHERE ba.COL8 = 617617
OR EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617)
OR EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617)
AND ba.COL4 <> 3
AND pcl.COL4 <> 3
AND pscl.COL4 <> 3
AND NOT EXISTS
...
您尚未执行任何操作来修改默认条件优先级。您可能只需要添加括号：
WHERE
(
ba.COL8 = 617617
OR EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617)
OR EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617)
)
AND ba.COL4 <> 3
AND pcl.COL4 <> 3
AND pscl.COL4 <> 3
AND NOT EXISTS
...
当然，未经测试，因为我们没有您的表格或数据，但这是一个首先要解决的简单问题。虽然这主要是一个逻辑问题 - 无论其他条件如何，您都将包含具有匹配 T6 或 T7 行的数据 - 但它似乎也可能导致至少一些性能问题。
评论
0赞
Dani Che
10/13/2023
非常感谢您的回复。我认为那里肯定需要括号。我现在尝试使用括号，但不幸的是，仍然是同样的问题。执行此 SELECT 需要“永远”。
上一个：透视未知字段值 [重复]
评论
WHERE ba.COL8 = 617617 OR EXISTS(SELECT 1 FROM T6 bba WHERE bba.COL7 = ba.COL7 AND bba.COL9 = 617617) OR (EXISTS(SELECT 1 FROM T7 bsa WHERE bsa.COL7 = ba.COL7 AND bsa.COL8 = 617617) AND ba.COL4 <> 3 AND...