2 Replies Latest reply: Aug 12, 2014 7:25 PM by user10923327 RSS

    Help optimizing this Paging and Sorting query


      Hi All,


      This query is running very slow (~5mins) to execute can any one suggest a way to optimized it. This query has three parts a) Count total record after filtering b) Get start to end record for paging and c) Collect all columns.


      SELECT * FROM (

        SELECT a.*, rownum row_num FROM (

          select myColumns

                  COUNT(*) OVER () RESULT_COUNT

          from tableOne t1, tableTwo t2

          where t1.id =t2.id

                and t2.party_id = :pId

                and t2.event_id = :eId 

                order by OPEN_DATE desc

          )a WHERE rownum < :end) WHERE row_num > = :start;