ORACLE QUERY - OPTIMISATION

Question

I have written the below query which has many 'AND' operators, i would like to know how to optimize the performance of the below query [can i remove some of the 'AND' Operators]

    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;

Answer 1

You can replace the 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 a little faster as it may reduce the number of full table scans and joins. But it's difficult to tell if it will be faster on your system since there are so many possible choices for the optimizer to make.

After cleaning up the query the next step for performance tuning is to generate an explain plan. Run explain plan for select ...; and then run select * from table(dbms_xplan.display); That will show you how the query is executed, which may give you a hint at what is slow and what can be improved. Add the full output of th explain plan to your question if you need more help. It may also help to add information about the number of rows in the relevant tables, how many rows are returned, and what are the indexes.

Answer 2

You can't combine any of the conditions as far as I can tell, but you can improve the query and reduce the number of AND operators by using standard JOIN syntax:

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 that as a basis, you may get an optimization boost if you join to the subquery conditions rather than using correlated subqueries. No guarantees, but something like this may 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;