RE: Simple question about nvl or-expansion

Is t.column1 constrained to not null?  

select * from t

where t.column1 like '%'  

will NOT return rows where t.column1 is NULL, so your transformations are not relationally equivalent unless there is a not null constraint on t.column1.  

likewise  

select * from t where t.column1 like t.column1;  

Unless I didn't have enough coffee today, but I think that is correct.  

So they are not the same query, but I don't know exactly how the CBO evaluates that without running a Wolfgang trace.  

I suggest you run the Wolfgang trace yourself on a small test set.  

Good luck. Quite possibly JL knows this off the top of his head.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of McPeak, Matt (Consultant)
Sent: Thursday, August 22, 2019 1:56 PM
To: oracle-l_at_freelists.org
Subject: Simple question about nvl or-expansion  

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    

--
http://www.freelists.org/webpage/oracle-l