RE: OR Expansion

It IS possible that the pieces of the UNION ALL are disjoint. YOU might know that they are disjoint without there being a reasonable way to inform the CBO that they are disjoint.  

LNNVL is one of my favorites. While it is often used under the covers, it has gone back and forth being deprecated and called a hack versus being approved and applauded.  

In the situation where only half of X=1 or Y=1 is known to be true, the UNION ALL can be ensured as valid by adding to the result of each piece “MINUS … where X=1 AND Y=1. (but YOU don’t need this if YOU know they are disjoint. The CBO could possibly make use of that validation).  

Then the question becomes whether or not you can inject this code in a way that the vacuous zero rows returned for the MINUS set is fast. This can be generalized to n pieces UNION ALL’d together.  

In the situation where the pieces are NOT disjoint, then for UNION ALL to be valid one of the two pieces needs to have the relevant MINUS. For simple OR conditions this *should* always be faster than sorting deduplication using UNION without the ALL. (roughly a maximum of 2n versus n log n, but much faster if indeed the MINUS is quick(n plus a little). And, if any of the pieces contain valid completely duplicate rows, using UNION is also not equivalent.  

When x=1 and y=1 and… z=1 are in fact disjoint it is easy to write the same MINUS predicate for all the pieces, but the complexity rises with the number of pieces being OR’d together (as does writing it in the LLNVL equivalent.)  

I’ll be so bold as to say that most humans will more easily see that this (correctly) enforces that the pieces of the UNION ALL are in fact disjoint than getting used to following the meaning of LNNVL and applying it correctly. LNNVL applied correctly should always be at least as fast as the MINUS way of writing it up, and sometimes producing the zero (or more in the non-disjoint case) rows returned for the MINUS can be as expensive as getting the keepers (getting all [or most of] the rows and filtering them out). In theory Oracle should sometimes be able to generate the fastest way to do this if it has all the required relationships and statistics in hand, but I think it is a big ask of the CBO.  

Now please notice that Laurentiu Oprea is correct that: “An important aspect to be aware of or expansion is not equivalent with writing the query using UNION ALL as you can change the query result. You must include filtering using LNNVL function” for the general case. I am calling out a special case where it is equivalent (because the results of the ORs are disjoint) and a possibly more tractable way to write the text equivalent of the LNNVL filters by using MINUS.  

You can get used to writing LNNVL correctly. Comments in the code should be copius, because this is a classic sort of thing where reading it six month later requires noodling out what it is intended to do all over again. (Which is worse. When originally written, you knew what you were trying to accomplish and crafted the correct code. Deriving the purpose from the code can be much more difficult unless a reminder is in the comments.)  

Good luck, (notice that none of this invalidates what Stephan and JL wrote, either),  

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea Sent: Tuesday, April 30, 2024 6:27 AM
Cc: oracle-l_at_freelists.org
Subject: Re: OR Expansion  

An important aspect to be aware of or expansion is not equivalent with writing the query using UNION ALL as you can change the query result. You must include filtering using LNNVL function.  

Now in regards to what you want to achieve, it is highly probable to be technically possible (if I understood correctly the question).

What you can do is either build the query yourself by replacing all the view names with the select definition or you do a run of the query using 10053 enabled and extract final query after transformation (ideally to do some checks to be sure is correct you might have surprises)  

Once you have this query you play around with hints in the code and do quick runs until you obtain the plan you need , you extract the outline of the resulted plan and try it into the original code. If works you can either create profile with hints required (or even with full outline if just the or_expand hints are ignored )  

Good luck.  

<snip>

--
http://www.freelists.org/webpage/oracle-l