2 Replies Latest reply: Dec 18, 2013 6:39 PM by jihuyao RSS

    Another way to write this SQL?


      This is a SQL statement that was caught by our DBA of a poor performing SQL that is created with a Hibernate Critera.  The Critera is done based on the user passing in for the search critera.  The idea is to get the inside select and have that return in the order of P_UNI - then we need to get only 100 rows back at a time.  The GUI part will pass in a UNI which then gets built into the Hibernate Critera to select the next 100 rows...


      Any one have suggestions on improving this SQL but continue to function as the business needs?




      SELECT * 
        FROM (SELECT   /*+ FIRST_ROWS(100) */ 
                       p.p_uni AS pio1_101_0_, 
                       p.created_ts AS created2_101_0_, 
                       p.updated_ts AS updated3_101_0_, 
                       p.msg_org AS msg7_101_0_, 
                       p.msg_type AS msg8_101_0_, 
                       p.msg_id AS msg9_101_0_, 
                       p.msg_info AS msg10_101_0_, 
                       p.status AS status101_0_, 
                       p.ERROR_CODE AS error14_101_0_, 
                       p.error_msg AS error15_101_0_ 
                  FROM table p 
                 WHERE 1 = 1 
                   AND (p.ERROR_CODE IS NULL OR p.ERROR_CODE <> 'DELETED') 
                   AND p.msg_type = :1 
              ORDER BY p.p_uni DESC) 
       WHERE ROWNUM < 101