To the Oracle gurus that dwell here:
I recently came across a query with a predicate containing this line:
AND t.column1 LIKE nvl(:b1,'%')
The query in question performed very poorly when :b1 was null. Changing it to
AND t.column1 LIKE nvl(:b1,t.column1)
.. improved it immensely and I could see the plan changed to benefit from nvl
or-expansion. Similar variants were all equally effective at fixing the
performance, e.g.:
AND ( t.column1 LIKE :b1 OR :b1 IS NULL )
My question is: what is the reason why Oracle's CBO was not able to use nvl
or-expansion in the original version? Is it just "they didn't implement it
that way"? Or is there something fundamental that makes it impossible?
Thanks,
Matt