1 2 Previous Next 16 Replies Latest reply: Dec 1, 2012 4:50 AM by chris227 Go to original post RSS
      • 15. Re: tuning regexp_like
        949210
        chris227,
        SELECT PROD_DETAILS,SIGN,ROWNUM FROM( SELECT PROD_DETAILS,SIGN,
         (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS 
         SIGN2 FROM (SELECT PROD_DETAILS,SIGN FROM  
         MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) 
         order by SIGN2,SIGN desc) where rownum<15
        this is the query now
        a function based index was created on lower(prod_details)
        like in this case is slower than regexp_like
        yes i have tested all the suggestions
        • 16. Re: tuning regexp_like
          chris227
          NO time at them moment, just some thoughts:
          rownum blongs to the inner query and must be used in the outer with an alias.
          THe fbi on lowee(prod_details cant be used if you use regexp_like. It can be used with like. So i am wonderning why this should be slowewr.
          SELECT
           PROD_DETAILS
          ,SIGN
          ,r
           FROM(
           SELECT ROWNUM r, PROD_DETAILS,SIGN,
           (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS 
           SIGN2 FROM (SELECT PROD_DETAILS,SIGN FROM  
           MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) 
           order by SIGN2,SIGN desc) where rownum<15
          1 2 Previous Next