需要有关优化SQL select语句的帮助
需要有关优化SQL select语句的帮助
提问于 2015-06-29 04:21:26
我是Oracle数据库的新手。我得到一个'select‘语句的性能问题。问题如下：
原始语句(运行速度极慢)：
SELECT *
FROM my_pos pos
WHERE my_source NOT IN
(SELECT my_source_id FROM my_source WHERE can_delete = 0
)
AND EXISTS
(SELECT 1
FROM my_agreement agr,
my_account acc,
my_account fund_acc,
my_client cli,
WHERE (agr.agr_client_acc_id = pos.my_acc_id
OR agr.agr_cp_acc_id = pos.my_acc_id
OR agr.agr_client_coll_acc_id = pos.my_acc_id
OR agr.agr_pool_acc_id = pos.my_acc_id
OR agr.client_pool_acc_id = pos.my_acc_id )
AND agr.agr_client_acc_id = acc.my_acc_id
AND acc.fund_acc_id = fund_acc.my_acc_id(+)
AND cli.client_id = (
CASE
WHEN fund_acc.my_acc_id IS NOT NULL
THEN fund_acc.client_id
ELSE acc.client_id
END )
);解释原始语句的计划：
Plan hash value: 4147965473
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1748 | 290K| 2532 (4)| 00:00:31 |
|* 1 | HASH JOIN SEMI | | 1748 | 290K| 2532 (4)| 00:00:31 |
|* 2 | HASH JOIN RIGHT ANTI | | 1748 | 268K| 1364 (2)| 00:00:17 |
|* 3 | TABLE ACCESS FULL | MY_SOURCE | 44 | 264 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MY_POS | 8738 | 1288K| 1361 (2)| 00:00:17 |
| 5 | VIEW | VW_SQ_1 | 16285 | 206K| 1167 (6)| 00:00:15 |
| 6 | UNION-ALL | | | | | |
| 7 | NESTED LOOPS | | 3257 | 78168 | 211 (7)| 00:00:03 |
|* 8 | HASH JOIN OUTER | | 3257 | 68397 | 209 (6)| 00:00:03 |
|* 9 | HASH JOIN | | 3257 | 45598 | 107 (6)| 00:00:02 |
| 10 | INDEX FAST FULL SCAN| IX_AGR_CLIENT | 3257 | 13028 | 4 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 12 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 13 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 3257 | 91196 | 238 (6)| 00:00:03 |
|* 15 | HASH JOIN OUTER | | 3257 | 81425 | 236 (5)| 00:00:03 |
|* 16 | HASH JOIN | | 3257 | 58626 | 135 (6)| 00:00:02 |
| 17 | TABLE ACCESS FULL | MY_AGREEMENT | 3257 | 26056 | 32 (4)| 00:00:01 |
| 18 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 19 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 20 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 3257 | 84682 | 239 (6)| 00:00:03 |
|* 22 | HASH JOIN OUTER | | 3257 | 74911 | 236 (5)| 00:00:03 |
|* 23 | HASH JOIN | | 3257 | 52112 | 135 (6)| 00:00:02 |
| 24 | TABLE ACCESS FULL | MY_AGREEMENT | 3257 | 19542 | 32 (4)| 00:00:01 |
| 25 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 26 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 27 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 28 | NESTED LOOPS | | 3257 | 84682 | 239 (6)| 00:00:03 |
|* 29 | HASH JOIN OUTER | | 3257 | 74911 | 236 (5)| 00:00:03 |
|* 30 | HASH JOIN | | 3257 | 52112 | 135 (6)| 00:00:02 |
| 31 | TABLE ACCESS FULL | MY_AGREEMENT | 3257 | 19542 | 32 (4)| 00:00:01 |
| 32 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 33 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 34 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 3257 | 84682 | 240 (7)| 00:00:03 |
|* 36 | HASH JOIN OUTER | | 3257 | 74911 | 237 (6)| 00:00:03 |
|* 37 | HASH JOIN | | 3257 | 52112 | 136 (6)| 00:00:02 |
| 38 | TABLE ACCESS FULL | MY_AGREEMENT | 3257 | 19542 | 33 (7)| 00:00:01 |
| 39 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 40 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 41 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("VW_COL_1"="POS"."MY_ACC_ID")
2 - access("MY_SOURCE"="MY_SOURCE_ID")
3 - filter("CAN_DELETE"=0)
8 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
9 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
13 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL)
THEN "FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
15 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
16 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
20 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL)
THEN "FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
22 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
23 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
27 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL)
THEN "FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
29 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
30 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
34 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL)
THEN "FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
36 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
37 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
41 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL)
THEN "FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )新语句(运行速度极快)：
SELECT *
FROM my_pos pos1
WHERE my_source NOT IN
(SELECT my_source_id FROM my_source WHERE can_delete = 0
)
AND EXISTS
(SELECT 1
FROM my_agreement agr,
my_account acc,
my_account fund_acc,
my_client cli,
-- add my_pos here
my_pos pos
WHERE (agr.agr_client_acc_id = pos.my_acc_id
OR agr.agr_cp_acc_id = pos.my_acc_id
OR agr.agr_client_coll_acc_id = pos.my_acc_id
OR agr.agr_pool_acc_id = pos.my_acc_id
OR agr.client_pool_acc_id = pos.my_acc_id )
AND agr.agr_client_acc_id = acc.my_acc_id
AND acc.fund_acc_id = fund_acc.my_acc_id(+)
AND cli.client_id = (
CASE
WHEN fund_acc.my_acc_id IS NOT NULL
THEN fund_acc.client_id
ELSE acc.client_id
END )
-- connect pos1 and pos
AND pos1.my_pos_id = pos.my_pos_id
);解释新语句的计划：
Plan hash value: 2962711282
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1748 | 290K| 9174 (2)| 00:01:51 |
|* 1 | HASH JOIN SEMI | | 1748 | 290K| 9174 (2)| 00:01:51 |
|* 2 | HASH JOIN RIGHT ANTI | | 1748 | 268K| 1364 (2)| 00:00:17 |
|* 3 | TABLE ACCESS FULL | MY_SOURCE | 44 | 264 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | MY_POS | 8738 | 1288K| 1361 (2)| 00:00:17 |
| 5 | VIEW | VW_SQ_1 | 32799 | 416K| 7809 (2)| 00:01:34 |
| 6 | CONCATENATION | | | | | |
|* 7 | HASH JOIN | | 1277 | 54911 | 1439 (2)| 00:00:18 |
| 8 | NESTED LOOPS | | 25 | 850 | 83 (3)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 25 | 775 | 83 (3)| 00:00:01 |
| 10 | NESTED LOOPS | | 25 | 600 | 58 (4)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | MY_AGREEMENT | 25 | 350 | 33 (7)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| MY_ACCOUNT | 1 | 10 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_MY_ACCOUNT | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | MY_ACCOUNT | 1 | 7 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_MY_ACCOUNT | 1 | | 0 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | MY_POS | 8738 | 78642 | 1356 (2)| 00:00:17 |
|* 18 | HASH JOIN | | 4956 | 208K| 1583 (2)| 00:00:19 |
| 19 | NESTED LOOPS | | 97 | 3298 | 227 (1)| 00:00:03 |
| 20 | NESTED LOOPS OUTER | | 97 | 3007 | 227 (1)| 00:00:03 |
| 21 | NESTED LOOPS | | 97 | 2328 | 129 (1)| 00:00:02 |
|* 22 | TABLE ACCESS FULL | MY_AGREEMENT | 97 | 1358 | 32 (4)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID| MY_ACCOUNT | 1 | 10 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_MY_ACCOUNT | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | MY_ACCOUNT | 1 | 7 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_MY_ACCOUNT | 1 | | 0 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | MY_POS | 8738 | 78642 | 1356 (2)| 00:00:17 |
|* 29 | HASH JOIN | | 8736 | 366K| 1594 (2)| 00:00:20 |
| 30 | NESTED LOOPS | | 776 | 26384 | 237 (6)| 00:00:03 |
|* 31 | HASH JOIN OUTER | | 776 | 24056 | 236 (5)| 00:00:03 |
|* 32 | HASH JOIN | | 776 | 18624 | 135 (6)| 00:00:02 |
|* 33 | TABLE ACCESS FULL | MY_AGREEMENT | 776 | 10864 | 32 (4)| 00:00:01 |
| 34 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 35 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 36 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS FULL | MY_POS | 8738 | 78642 | 1356 (2)| 00:00:17 |
|* 38 | HASH JOIN | | 8733 | 366K| 1596 (2)| 00:00:20 |
| 39 | NESTED LOOPS | | 3075 | 102K| 239 (6)| 00:00:03 |
|* 40 | HASH JOIN OUTER | | 3075 | 95325 | 237 (6)| 00:00:03 |
|* 41 | HASH JOIN | | 3075 | 73800 | 136 (6)| 00:00:02 |
|* 42 | TABLE ACCESS FULL | MY_AGREEMENT | 3075 | 43050 | 33 (7)| 00:00:01 |
| 43 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 44 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 45 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 46 | TABLE ACCESS FULL | MY_POS | 8738 | 78642 | 1356 (2)| 00:00:17 |
|* 47 | HASH JOIN | | 9097 | 382K| 1596 (2)| 00:00:20 |
| 48 | NESTED LOOPS | | 3257 | 108K| 240 (7)| 00:00:03 |
|* 49 | HASH JOIN OUTER | | 3257 | 98K| 237 (6)| 00:00:03 |
|* 50 | HASH JOIN | | 3257 | 78168 | 136 (6)| 00:00:02 |
| 51 | TABLE ACCESS FULL | MY_AGREEMENT | 3257 | 45598 | 33 (7)| 00:00:01 |
| 52 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 226K| 101 (4)| 00:00:02 |
| 53 | TABLE ACCESS FULL | MY_ACCOUNT | 23210 | 158K| 100 (3)| 00:00:02 |
|* 54 | INDEX UNIQUE SCAN | PK_CLIENT | 1 | 3 | 0 (0)| 00:00:01 |
| 55 | TABLE ACCESS FULL | MY_POS | 8738 | 78642 | 1356 (2)| 00:00:17 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("POS1"."MY_POS_ID"="ITEM_1")
2 - access("MY_SOURCE"="MY_SOURCE_ID")
3 - filter("CAN_DELETE"=0)
7 - access("AGR"."CLIENT_POOL_ACC_ID"="POS"."MY_ACC_ID")
11 - filter("AGR"."CLIENT_POOL_ACC_ID" IS NOT NULL)
13 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
15 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
16 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL) THEN
"FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
18 - access("AGR"."AGR_POOL_ACC_ID"="POS"."MY_ACC_ID")
filter(LNNVL("AGR"."CLIENT_POOL_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."CLIENT_POOL_ACC_ID" IS NOT NULL))
22 - filter("AGR"."AGR_POOL_ACC_ID" IS NOT NULL)
24 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
26 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
27 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL) THEN
"FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
29 - access("AGR"."AGR_CLIENT_COLL_ACC_ID"="POS"."MY_ACC_ID")
filter((LNNVL("AGR"."AGR_POOL_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."AGR_POOL_ACC_ID" IS NOT NULL)) AND
(LNNVL("AGR"."CLIENT_POOL_ACC_ID"="POS"."MY_ACC_ID") OR LNNVL("AGR"."CLIENT_POOL_ACC_ID" IS
NOT NULL)))
31 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
32 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
33 - filter("AGR"."AGR_CLIENT_COLL_ACC_ID" IS NOT NULL)
36 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL) THEN
"FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
38 - access("AGR"."AGR_CP_ACC_ID"="POS"."MY_ACC_ID")
filter((LNNVL("AGR"."AGR_CLIENT_COLL_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."AGR_CLIENT_COLL_ACC_ID" IS NOT NULL)) AND
(LNNVL("AGR"."AGR_POOL_ACC_ID"="POS"."MY_ACC_ID") OR LNNVL("AGR"."AGR_POOL_ACC_ID" IS NOT
NULL)) AND (LNNVL("AGR"."CLIENT_POOL_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."CLIENT_POOL_ACC_ID" IS NOT NULL)))
40 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
41 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
42 - filter("AGR"."AGR_CP_ACC_ID" IS NOT NULL)
45 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL) THEN
"FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )
47 - access("AGR"."AGR_CLIENT_ACC_ID"="POS"."MY_ACC_ID")
filter((LNNVL("AGR"."AGR_CP_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."AGR_CP_ACC_ID" IS NOT NULL)) AND
(LNNVL("AGR"."AGR_CLIENT_COLL_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."AGR_CLIENT_COLL_ACC_ID" IS NOT NULL)) AND
(LNNVL("AGR"."AGR_POOL_ACC_ID"="POS"."MY_ACC_ID") OR LNNVL("AGR"."AGR_POOL_ACC_ID" IS NOT
NULL)) AND (LNNVL("AGR"."CLIENT_POOL_ACC_ID"="POS"."MY_ACC_ID") OR
LNNVL("AGR"."CLIENT_POOL_ACC_ID" IS NOT NULL)))
49 - access("ACC"."FUND_ACC_ID"="FUND_ACC"."MY_ACC_ID"(+))
50 - access("AGR"."AGR_CLIENT_ACC_ID"="ACC"."MY_ACC_ID")
54 - access("CLI"."CLIENT_ID"=CASE WHEN ("FUND_ACC"."MY_ACC_ID" IS NOT NULL) THEN
"FUND_ACC"."CLIENT_ID" ELSE "ACC"."CLIENT_ID" END )我的新select语句比旧的快得多(快80倍！)，但我不知道为什么。
我只是将目标表添加到子语句中(新的insert语句紧跟在我的注释之后)，并多次运行这两个语句。它们给我的结果都是一样的。但是，原来的平均耗时80秒，新的平均耗时1秒。有没有人能告诉我为什么会这样？大多数细节都是欢迎的。
致以最好的敬意。
回答 3
Stack Overflow用户
发布于 2015-06-29 05:34:03
我将从查看您的语句的execution plans开始。
1)创建执行计划：
explain plan for (select * from table_name where ...);2)显示执行计划：
select * from table(dbms_xplan.display);在sql*plus中，您可能还希望只使用AUTO TRACE选项。
这使您可以看到Oracle是如何执行语句的，并且是解决语句级任何性能问题的起点。
Stack Overflow用户
发布于 2018-11-12 00:44:17
在Oracle SQL调优之旅的第一步中做得很好！不幸的是，你的问题的答案并不直接，还有很多工作要做。您的第二个查询可能看起来运行得很快，但我怀疑如果我们增加数据量和/或执行频率，它是否会有效地扩展。
您应该始终使用ANSI-92SQL标准编写查询。这是一个comparison。这不仅有助于更好地理解您的连接，而且对您的调优工作也有很大帮助。
我质疑可伸缩性的另一个原因是WHERE my_source NOT IN子句。在Oracle中，您必须小心，不要允许IN列表达到1000的硬限制，否则可能会出现ORA-01795: maximum number of expressions in a list is 1000错误。从调优的角度来看，IN列表应该是固定值，并且长度非常有限。如果您需要更多的值，那么可以考虑将它们存储在一个带有索引的表中。使用EXISTS并使用密钥连接，而不是使用NOT IN。这允许数据库利用索引，而不是将子查询的整个结果存储在内存中。它可能在一小部分数据上运行得很快，但如果数据量/执行次数增加，最终会使用更多宝贵的数据库内存。在极端情况下，使用大量内存的大量查询会显著降低生产数据库的速度。
另一件需要考虑的事情是WHERE子句中的CASE语句。Oracle在这里使用索引的可能性要小得多，因为每一行都使用一个表达式。对每一行使用一个表达式可能就是您在解释计划中看到TABLE ACCESS FULL和许多NESTED LOOPS的原因。嵌套循环和全表扫描的出现是因为Oracle尽量将数据收集到一个动态视图(VW_SQ_1)中，然后在最后使用HASH JOIN将所有数据混合在一起。所有这些额外的工作都是以CPU、内存(字节)、磁盘IO和时间为代价的。
执行全表扫描不一定是一件坏事，前提是您的意图是处理每一行，并且扫描不会嵌套在其他循环中太深。但是，如果数据量很大，数据库必须执行大量繁重的任务来扫描所有行。在这种情况下，需要对SQL进行重大重写(或重新设计表/索引)。
对于较小的逻辑子查询，SQL总是执行得更好。大型、复杂子查询的调优可能非常麻烦。尽可能删除OR语句，这样查询更容易理解。包含UNION的CTE是实现这一点的好方法，因为您只需要查找一行的存在。请记住，UNION是UNION ALL和DISTINCT的组合。您可以进一步调整为只使用UNION ALL，但需要更多的数据知识。
在左连接的结果出现之前，请注意不要尝试在WHERE子句中过滤NULL，除非您知道数据中存在NULL。我在这里的假设是id不包含NULL。
最后，保持你的谓词“真实”，而不是“虚假”。或者换句话说，总是尝试让您的逻辑搜索TRUE结果，在可能的情况下使用=、AND、EXISTS和INNER JOINS。将<>、OR和NOT EXISTS的使用率保持在最低水平。优先使用LEFT JOIN而不是RIGHT JOIN (或任何其他奇特的连接方法)，除非您有充分的理由并且知道自己在做什么。
WITH Categories AS
(
SELECT
fund_acc.my_acc_id,
acc.client_id
FROM my_pos pos
INNER JOIN my_agreement agr ON agr.agr_client_acc_id = pos.my_acc_id
INNER JOIN my_account acc ON acc.my_acc_id = agr.agr_client_acc_id
INNER JOIN my_client cli ON cli.client_id = fund_acc.client_id
LEFT JOIN my_account fund_acc ON fund_acc.my_acc_id = acc.fund_acc_id
WHERE pos.my_pos_id = pos1.my_pos_id
UNION
SELECT
fund_acc.my_acc_id,
acc.client_id
FROM my_pos pos
INNER JOIN my_agreement agr ON agr.agr_cp_acc_id = pos.my_acc_id
INNER JOIN my_account acc ON acc.my_acc_id = agr.agr_client_acc_id
INNER JOIN my_client cli ON cli.client_id = fund_acc.client_id
LEFT JOIN my_account fund_acc ON fund_acc.my_acc_id = acc.fund_acc_id
WHERE pos.my_pos_id = pos1.my_pos_id
UNION
SELECT
fund_acc.my_acc_id,
acc.client_id
FROM my_pos pos
INNER JOIN my_agreement agr ON agr.agr_client_coll_acc_id = pos.my_acc_id
INNER JOIN my_account acc ON acc.my_acc_id = agr.agr_client_acc_id
INNER JOIN my_client cli ON cli.client_id = fund_acc.client_id
LEFT JOIN my_account fund_acc ON fund_acc.my_acc_id = acc.fund_acc_id
WHERE pos.my_pos_id = pos1.my_pos_id
UNION
SELECT
fund_acc.my_acc_id,
acc.client_id
FROM my_pos pos
INNER JOIN my_agreement agr ON agr.agr_pool_acc_id = pos.my_acc_id
INNER JOIN my_account acc ON acc.my_acc_id = agr.agr_client_acc_id
INNER JOIN my_client cli ON cli.client_id = fund_acc.client_id
LEFT JOIN my_account fund_acc ON fund_acc.my_acc_id = acc.fund_acc_id
WHERE pos.my_pos_id = pos1.my_pos_id
UNION
SELECT
fund_acc.my_acc_id,
acc.client_id
FROM my_pos pos
INNER JOIN my_agreement agr ON agr.client_pool_acc_id = pos.my_acc_id
INNER JOIN my_account acc ON acc.my_acc_id = agr.agr_client_acc_id
INNER JOIN my_client cli ON cli.client_id = fund_acc.client_id
LEFT JOIN my_account fund_acc ON fund_acc.my_acc_id = acc.fund_acc_id
WHERE pos.my_pos_id = pos1.my_pos_id
)
SELECT
*
FROM my_pos pos1
WHERE NOT EXISTS (
SELECT 1
FROM my_source mys
WHERE mys.my_source_id = pos1.my_source
AND mys.can_delete = 0
)
AND (
EXISTS (
SELECT
c.my_acc_id
FROM Categories c
INNER JOIN my_client cli ON cli.client_id = c.my_acc_id
WHERE c.my_acc_id IS NOT NULL
)
OR EXISTS
(
SELECT
c.client_id
FROM Categories c
INNER JOIN my_client cli ON cli.client_id = c.client_id
WHERE c.my_acc_id IS NULL
)
);免责声明:我可以继续调优这条语句。但是如果不了解数据、需求和表结构，我可能就到此为止了。这个查询可能有一些but，但我要演示的是如何组织您的SQL，以便您可以在将每个片段组合成一个整体之前对其进行测试。
Stack Overflow用户
发布于 2015-06-29 05:50:22
我相信，一旦您在查询中添加了‘AND pos1.my_pos_id = pos.my_pos_id’语句，oracle就可以将该查询视为"inner“，执行第三个select语句一次，并使用索引(&外键)来决定返回哪些记录。在第一个查询中，对源表(my_pos)中的每条记录分别执行select语句
您的查询等于：
SELECT *
FROM my_pos pos1
left join
my_sourc m on m.my_source_id=pos1.my_source
inner join
(SELECT my_pos_id
FROM my_agreement agr,
my_account acc,
my_account fund_acc,
my_client cli,
-- add my_pos here
my_pos pos
WHERE (agr.agr_client_acc_id = pos.my_acc_id
OR agr.agr_cp_acc_id = pos.my_acc_id
OR agr.agr_client_coll_acc_id = pos.my_acc_id
OR agr.agr_pool_acc_id = pos.my_acc_id
OR agr.client_pool_acc_id = pos.my_acc_id )
AND agr.agr_client_acc_id = acc.my_acc_id
AND acc.fund_acc_id = fund_acc.my_acc_id(+)
AND cli.client_id = (
CASE
WHEN fund_acc.my_acc_id IS NOT NULL
THEN fund_acc.client_id
ELSE acc.client_id
END )
)
-- connect pos1 and pos
on pos1.my_pos_id = pos.my_pos_id
where pos1.my_source is null -- this row says, my source not in my source table there for I accept only nulls 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31107730
复制相关文章
相似问题
需要有关优化SQL语句的帮助
需要有关SELECT语句的帮助
需要有关条件SELECT语句的帮助
需要有关SQL语句的帮助
有关选择MS Sql select语句的帮助
添加站长 进交流群
领取专属 10元无门槛券
AI混元助手 在线答疑
关注 腾讯云开发者公众号
洞察 腾讯核心技术
剖析业界实践案例
社区富文本编辑器全新改版！诚邀体验～
全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验
腾讯云开发者
