提问人：Dani Che 提问时间：10/11/2023 最后编辑：Dani Che 更新时间：10/30/2023 访问量：90
如何改进这个 SELECT？
How to improve this SELECT?
问：
是否可以以某种方式重写此选择以加快执行速度？你有什么建议？从哪里开始寻找...？任何帮助将不胜感激。
我不明白为什么执行计划的第 19 行中有“TABLE ACCESS FULL”，因为 COL7 在 T1 （“ba”） 中是 PK，而在 T6 （“bba”） 中有一个唯一的索引（组合索引还有 1 列）。
另外，T2 在 COL4 上没有索引，所以我添加了它，但执行计划是相同的。什么都没有改变。第 21 行和第 22 行仍为全表扫描。为什么？
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;
数据库版本为 Oracle 19c 企业版。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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")
表名和列名经过仔细匿名化处理。我希望你不要介意。
答：
1赞
FriendlyDragon
10/30/2023
#1
正如你所看到的，所有带有索引的工作都非常快。最可怕的问题是表 T1、T2 和 T6 - 他们都可以访问 - 这是非常糟糕的。TABLE ACCESS FULL
当然，他们应该有索引，但这的条件真的很奇怪......(ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617), ba.COL4 <> 3,
正如我们在评论中讨论的那样，它可能是问题的根源，因此，为了避免并且如果您只需要这些值之一，则可以使用 ，如下所示。FULL ACCESSUNION
如果你对 case1 有一些这样的：OR
with q as (
select id, name
from table
where id in (5, 6, 7)
)
select *
from q
对于 Case2，您可以将其转换为如下所示的内容：
with q as (
select id, name
from table
where id = 5
union all
select id, name
from table
where id = 6
union all
select id, name
from table
where id = 7
)
select *
from q
因此，如果 2 数据库能够为您的表使用索引。
评论
bsa.COL8 = 617617TABLE ACCESS FULL(ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617)ba.COL4 <> 3