Forum Stats

  • 3,770,915 Users
  • 2,253,181 Discussions
  • 7,875,654 Comments

Discussions

How to re-write query for better performance.

Anand Yadav
Anand Yadav Member Posts: 90 Blue Ribbon

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?

Answers