RE: Simple question about nvl or-expansion

From: Mark W. Farnham <>
Date: Thu, 22 Aug 2019 14:49:38 -0400
Message-ID: <032b01d5591a$5a0d23d0$0e276b70$>

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.  


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.  


From: [] On Behalf Of McPeak, Matt (Consultant)
Sent: Thursday, August 22, 2019 1:56 PM
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?  



Received on Thu Aug 22 2019 - 20:49:38 CEST

Original text of this message