RE: Simple question about nvl or-expansion

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 22 Aug 2019 21:46:06 -0400
Message-ID: <037901d55954$886038f0$9920aad0$_at_rsiz.com>

I think you've had just the correct amount of wine...

The rows would drop out in either case is the correct answer, so the meaning did not change.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, August 22, 2019 3:36 PM
To: oracle-l_at_freelists.org
Subject: Re: Simple question about nvl or-expansion

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 23 2019 - 03:46:06 CEST