RE: Simple question about nvl or-expansion

Yes, t.column1 is not null. Sorry I should have known to include that in the OP.

From: Mark W. Farnham <mwf_at_rsiz.com>
Sent: Thursday, August 22, 2019 2:50 PM
To: McPeak, Matt (Consultant) <vxsmimmcp_at_subaru.com>; oracle-l_at_freelists.org Subject: RE: Simple question about nvl or-expansion

CAUTION: This email has originated from outside of SOA. Do not click on links or open attachments unless you recognize the sender and know the content is safe.

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> [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<mailto: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