Hi All,
I'm facing performance issue with below query. table oicv having separate indexes on f_code, vp_number columns and on vp_number. But still optimizer doing full table scan on oicv when i look the explain plan of below query.
Could you please help how to make below query use index ?
Below are couple of Oracle suggestions
Full scan of TABLE "OICV" with object ID 208388 consumed 88% of the database time spent on this SQL statement
The predicate CASE WHEN :B1 IS NOT NULL THEN CASE "V"."vp_number" WHEN UPPER(:B2) THEN 1 ELSE 0 END WHEN :B3 IS NULL THEN 1 END =1 used at line ID 18 of the execution plan contains an expression on indexed column "vp_number". This expression prevents the optimizer from selecting indices on table "OICV".
select ot.* FROM oit ot, oip op
WHERE ot.t_type = 'ABC'
AND ot.f_code = op.f_code
AND ot.t_number = op.t_number
AND ot.v_code = op.v_code
AND EXISTS
(SELECT 1 FROM oicv oiv
WHERE oiv.f_code= ot.f_code
and oiv.v_code = ot.v_code
AND CASE WHEN :pi_vp_number IS NOT NULL THEN
CASE WHEN oiv.vp_number = UPPER ( :pi_vp_number) THEN 1
ELSE 0 END
WHEN :pi_vp_number IS NULL THEN 1
END = 1
)