Re: Simple question about nvl or-expansion

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 22 Aug 2019 19:36:23 +0000
Message-ID: <LNXP265MB044391D692AC58D552FE3FC7A5A50_at_LNXP265MB0443.GBRP265.PROD.OUTLOOK.COM>

Sorry, Mark, I don't think you've had enough coffee this morning (or maybe I've had too much wine this evening).

Technically or_nvl_expansion would (probably) turn the query into:

select v1, v2 from t1 where :b1 is null and v1 like '%' union all
select v1, v2 from t1 where :b1 is not null and v1 like :b1 /

"column1 like '%'"

won't return the rows where column1 is null - true, but

"column1 like null"

won't return those rows either

So the or expansion would be valid.

I think this is just a case of "no-one's written the code yet".

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Mark W. Farnham <mwf_at_rsiz.com> Sent: 22 August 2019 19:49
To: vxsmimmcp_at_subaru.com; oracle-l_at_freelists.org Subject: 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

Received on Thu Aug 22 2019 - 21:36:23 CEST