4 Replies Latest reply: Feb 1, 2013 11:34 AM by Etbin RSS

    How to deal with case statement which prohibits index usage

    698658
      Hi,

      I'm on 11.2.0.3 and got query like this
      select * 
      FROM tab1 k,
                tab2 u,
                WHERE 
                 (CASE
                    WHEN (:b2 IS NOT NULL AND k.id_kontr = :b2)
                     OR (:b2 IS NULL AND k.cif_profile = :b1)
                       THEN 1
                    ELSE 0
                 END
                ) = 1
            AND k.id = u.tk_id
      So, k.id_kontr and k.cif_profile are very selective and indexed but Oracle is unable to use index on them instead we have full table scan and hash_join even expected output is few rows (0.1 %) .
      How to code that properly so we can use indexes .
      Regards
      GregG