ORACLE QUERY - OPTIMIZATION
I wrote the following query which has a lot of AND operators, I would like to know how to optimize the performance of the below query. Can some of the AND operators be removed?
SELECT I.date,
K.somcolumn,
L.somcolumn,
D.somcolumn
FROM Table1 I,
Table2 K,
Table3 L,
Table4 D
WHERE I._ID = K._ID
AND K.ID = L._ID
AND L._ID = I._ID
AND I._CODE = L._CODE
AND K.ID = D._ID(+)
AND L._ID IN ( SELECT _id
FROM I
WHERE UPPER (someflag) = 'TRUE'
GROUP BY _id
HAVING COUNT (*) > 1)
AND L._ID IN ( SELECT _id
FROM I
WHERE UPPER (code) = 'OPEN'
GROUP BY _id
HAVING COUNT (*) > 1)
ORDER BY I._ID, I._CODE;
source to share
You cannot combine any of the conditions as far as I can tell, but you can improve the query and reduce the number of statements AND
using the standard syntax JOIN
:
SELECT I.date,
K.somcolumn,
L.somcolumn,
D.somcolumn
FROM Table1 I
INNER JOIN Table2 K ON I._ID = K._ID
INNER JOIN Table3 L ON K.ID = L._ID
LEFT JOIN Table4 D ON K.ID = D._ID
WHERE L._ID IN ( SELECT _id
FROM I
WHERE UPPER (someflag) = 'TRUE'
GROUP BY _id
HAVING COUNT (*) > 1)
AND L._ID IN ( SELECT _id
FROM I
WHERE UPPER (code) = 'OPEN'
GROUP BY _id
HAVING COUNT (*) > 1)
ORDER BY I._ID, I._CODE;
With this as a basis, you can get an optimization boost if you join the subquery conditions rather than using correlated subqueries. No guarantees, but something like this might help:
SELECT I.date,
K.somcolumn,
L.somcolumn,
D.somcolumn
FROM Table1 I
INNER JOIN Table2 K ON I._ID = K._ID
INNER JOIN Table3 L ON K.ID = L._ID
LEFT JOIN Table4 D ON K.ID = D._ID
INNER JOIN (
SELECT _id
FROM I
WHERE UPPER (someflag) = 'TRUE'
GROUP BY _id
HAVING COUNT (*) > 1
) someflagtrue ON L._ID = someflagtrue._id
INNER JOIN (
SELECT _id
FROM I
WHERE UPPER (code) = 'OPEN'
GROUP BY _id
HAVING COUNT (*) > 1
) codeopen ON L._ID = codeopen._id
ORDER BY I._ID, I._CODE;
source to share
You can replace two subqueries with one.
Old subqueries:
SELECT _id
FROM I
WHERE UPPER (someflag) = 'TRUE'
GROUP BY _id
HAVING COUNT (*) > 1)
SELECT _id
FROM I
WHERE UPPER (code) = 'OPEN'
GROUP BY _id
HAVING COUNT (*) > 1)
New subquery:
SELECT _ID
FROM I
GROUP BY _ID
HAVING COUNT(CASE WHEN UPPER(SOMEFLAG) = 'TRUE' THEN 1 ELSE 0 END) > 0
AND COUNT(CASE WHEN UPPER(CODE) = 'OPEN' THEN 1 ELSE 0 END) > 0
In most cases, this should be at least slightly faster, as it can reduce the number of full scans and table joins. But it is difficult to say if it will be faster on your system as there are so many possible options for the optimizer.
After clearing the query, the next step for performance tuning is to create an explain plan. Run explain plan for select ...;
and then run select * from table(dbms_xplan.display);
. This will show you how the query is being executed, which can give you a hint about what is going slow and what could be improved. Add the full explanation plan to your question if you need more help. It can also help to add information about the number of rows in the respective tables, the number of rows, etc. Indices.
source to share