How to re-write query for better performance.

Anand Yadav

Hi all,

This query seems not written optimally but I'm not able to quickly re-write it and little confused.

SELECT  DISTINCT TB1.DMDUNIT, TB1.DMDGROUP, TB1.LOC

 FROM BASECOST TB1

 WHERE EXISTS

     (SELECT 1

       FROM BASECOST TB2

      WHERE   TB1.DMDUNIT = TB2.DMDUNIT

         AND TB1.DMDGROUP = TB2.DMDGROUP

         AND TB1.LOC = TB2.LOC

         AND TB2.STARTDATE > TB1.STARTDATE

         AND TB2.STARTDATE < (TB1.STARTDATE + (TB1.DUR / 1440))

         ANDTB2.STARTDATE =

                   TB1.STARTDATE + (TB1.DUR / 1440))

    OR (TB1.STARTDATE + (TB1.DUR / 1440) < SYSDATE

      AND NOT EXISTS

           (SELECT 1

             FROM BASECOST TB2

            WHERE   TB1.DMDUNIT = TB2.DMDUNIT

               AND TB1.DMDGROUP = TB2.DMDGROUP

               AND TB1.LOC = TB2.LOC

               AND TB2.STARTDATE =

                   TB1.STARTDATE + (TB1.DUR / 1440)));

Any inputs please without changing the functionality of query?

Robeen

Hi,

is the query slow? What does explain plan display?