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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of McPeak, Matt (Consultant)
Sent: Thursday, August 22, 2019 1:56 PM
To: oracle-l@xxxxxxxxxxxxx
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
