4 Replies Latest reply: Jul 29, 2013 10:22 AM by Pacmann RSS

    re-write a bad query

    user587133

      This query works fine for me but it's not a good query it might have performance issues.

       

      select count(*) from EMP.PENSIONS
      where FAILURE_ID = (SELECT max(FAILURE_ID) FROM EMP.PENSIONS ) and PERIOD = (select max(PERIOD) from EMP.PENSIONS )

       

      Can someone let me know the best way to re-write it ?

        • 1. Re: re-write a bad query
          meser

          Tuning form questions should include more info but I see one point that may help you.

           

          if you change it like this you will have to query the PENTIONS table only once and if you have an index on (FAILURE_ID, PERIOD ) that should be good for performance.

           

          select count(*) from EMP.PENSIONS

          where (FAILURE_ID, PERIOD ) IN (SELECT max(PERIOD) , max(FAILURE_ID) FROM EMP.PENSIONS )


          • 2. Re: re-write a bad query
            34MCA2K2

            Not tested

             

            select

            count(*)

            from

            (select

            rank() over (order by failure_id desc nulls last) max_failure_id

            rank() over (order by period desc nulls last) max_period

            from emp.pensions

            )

            where max_failure_id = 1

            and max_period = 1

            • 3. Re: re-write a bad query
              Hoek

              Whenever you have a tuning related question, please follow the steps explained here:

              http://oracle-randolf.blogspot.nl/2009/02/basic-sql-statement-performance.html

               

              Besides that, I recall having tuned a similar query successfully using the following approach, not tested, but (depending on your database version) maybe worth a try as well:

               

              select count(*)
              from   emp.pensions p
              where not exists ( select null
                                 from   emp.pensions p1
                                 where  p1.failure_id > p.failure_id
                                 and    p1.period > p.period
                               );
              
              • 4. Re: re-write a bad query
                Pacmann

                Hi,

                 

                It depends on how your table is indexed and the data distribution.

                 

                In some cases the other answers given to you might be better.

                 

                In other cases, for example failure_id and / or period are indexed (separately) and very selective, your query might perform better (not sure the other can take advantage of an index)