Reputation: 21
Need help about optimizing SQL select statement
I am a newbie of Oracle Database. I get a performance issue with 'select' statement. The problem is like below:
Original Statement ( works extremely slow):
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 )
);
Explain Plan for Original Statement:
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 )
New Statement ( works extremely fast):
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
);
Explain Plan for New Statement:
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 )
My new select statement works much more faster than the old one ( 80 times faster ! ), but I do not know why.
I just add the target table into the sub-statement ( the new insert sentences follow my comments ), and ran both statements several times. Both of them give me the same results. However, the original one cost 80 seconds in average and the new one cost 1 second in average. Is there any one could tell me why is this case? Most details are welcome.
Best regard.
Upvotes: 2
Views: 105
Answers (3)
Reputation: 783
Well done on your first steps in your Oracle SQL tuning journey! Unfortunately, the answer to your question isn't straight-forward and there is much work to do. Your second query may appear to run fast, but I doubt that it would scale effectively if we increase the data volumes and/or the frequency of execution.
You should always write your queries using the ANSI-92 SQL standard. Here's a comparison. This will not only help understand your joins better, but also greatly help with your tuning effort.
Another reason I question the scalability is the WHERE my_source NOT IN clause. In Oracle, you have to be careful not to allow an IN list to reach the hard limit of 1000,
or you may get the ORA-01795: maximum number of expressions in a list is 1000 error. From a tuning point of view, IN lists should be fixed values, and very limited in length.
If you need more values, then consider storing them in a table, with an index.
Instead of using NOT IN, use EXISTS and connect using the keys. This allows the database to take advantage of indexes, instead of storing the entire results of the subquery in memory.
It may run fast on a small set of data, but if the data volume/number of executions increases, you end up using a lot more of the precious database memory.
In extreme cases, high volume queries that use lots of memory can significantly slow down a production database.
Another thing to consider is the CASE statement within a WHERE clause. Oracle is much less likely to use an index here, because there is an expression being used for every row.
Using an expression for every row is probably why you are seeing TABLE ACCESS FULL and many NESTED LOOPS in the Explain Plan. Nested Loops and Full Table Scans appear as Oracle does its best
to collect the data into a dynamic view (VW_SQ_1), then using HASH JOINs to mash it all together at the end. All of this extra work comes at a cost, in CPU, memory (bytes), disk IO and Time.
Doing a full table scan is not necessarily a bad thing, providing your intention is to process every row and the scan isn't nested too deeply inside other loops. But if the volume is HUGE, the database has to do a lot of heavy lifting to scan all the rows. In this situation, a major rewrite of the SQL (or redesign of the tables/indexes) would be needed.
SQL always performs better with smaller, logical subqueries. Large, complex subqueries can be a real hassle to tune. Remove OR statements where you can, so the query is easier to understand.
A CTE containing a UNION is a good way to achieve this, because you are only looking for the existence of one row. Remember, a UNION is a UNION ALL combined with a DISTINCT.
You could tune this further to only use a UNION ALL, but greater knowledge of the data is needed.
Be careful to not try to filter on NULLs in a WHERE clause before the results of a LEFT JOIN have appeared, unless you know NULLs exist in the data. My assumption here is that the ids do not contain NULLs.
Finally, keep your predicates "truthy", not "falsey". Or in other words, always try to make your logic search for a TRUE result, by using =, AND, EXISTS and INNER JOINS where possible.
Keep your use of <> and OR and NOT EXISTS to a minimum. Favour LEFT JOIN over RIGHT JOIN (or any other fancy join method) unless you have a good reason and know what you are doing.
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
)
);
Disclaimer: I could continue tuning this statement. But without knowing the data, the requirements and the table structures I should probably stop here. This query may have some bugs, but what I'm trying to demonstrate is how to structure your SQL so that you can test each fragment before combining it into a whole.
Upvotes: 1
Reputation: 504
I believe that once you've add the ' AND pos1.my_pos_id = pos.my_pos_id' sentence in your query, oracle can treat the query as "inner join",- execute the third select statement one time, and use indexes ( &foreign keys) to decide which records to return. In your first query, the select statement executed separately for each record in the source table (my_pos)
Your query is equals to:
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
Upvotes: 0
Reputation: 10341
I would start by having a look to the execution plans of your statements.
1) Create an execution plan:
explain plan for (select * from table_name where ...);
2) Display the execution plan:
select * from table(dbms_xplan.display);
In sql*plus you might also want to use just use the AUTO TRACE option.
This allows you to see, how Oracle executes your statements and is the starting point to any performance problem on the statement level.
Upvotes: 1