Hi friends,
i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem:
select egp.bsn
, egp.klantnummer as "Persoonsnummer"
, egp.samengesteldenaam as "Samengesteldenaam"
, egp.geboortedatum as "Geboortedatum"
from pr_eigenschappen2 egp
where
(egp.bsn = :b1 or :b2 is null)
and rownum < 51
egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics.
when run with b1:=928818 and b2:=928818 (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2.
if the query is changed to
select egp.bsn
, egp.klantnummer as "Persoonsnummer"
, egp.samengesteldenaam as "Samengesteldenaam"
, egp.geboortedatum as "Geboortedatum"
from pr_eigenschappen2 egp
where
(egp.bsn = :b1 or 928818 is null)
and rownum < 51
the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.
if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.