This discussion is archived
4 Replies Latest reply: Feb 1, 2013 11:34 AM by Etbin RSS

How to deal with case statement which prohibits index usage

698658 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points