Can this select somehow be rewritten to execute faster? What would you suggest? Where to start looking for...? Any help will be greatly appreciated.
I don't understand why there is "TABLE ACCESS FULL" in the line 19 of the execution plan because COL7 is PK in T1 ("ba") and in T6 ("bba") has a unique index (combined index with 1 more column).
Also, T2 didn't had an index on COL4 so I added it, but the execution plan is the same. Nothing changed. Still full table scan at lines 21 and 22. Why?
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;
Version of the DB is Oracle 19c Enterprise Edition.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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")
Table and column names are carefully anonymized. I hope that you don't mind.
As you can see, all with indexes works very fast. The most terrible problems are with tables T1, T2 and T6 - all they have acces by TABLE ACCESS FULL - this is very bad.
Of course, they should have indexes, but conditions for this really strange... (ba.COL8 = 617617 OR bsa.COL8 = 617617 OR bba.COL9 = 617617), ba.COL4 <> 3,
As we discassed in comments, it can be a root of issue, so, to avoid FULL ACCESS and if you need exactly one of these values, you can use UNION, something like this.
If you have some OR like this for case1:
with q as (
select id, name
from table
where id in (5, 6, 7)
)
select *
from q
you can transform it to something like this for 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
So, in case 2 database will be able use indexes for your tables.