Re: use_concat question

I may have got my conjunctive and disjunctive normal forms back to front - the numbering might be after converting the predicate list to disjunctive normal form, not conjunctive normal form - the principle is similar, and not something you'd really want to do by hand in many cases.

Regards
Jonathan Lewis

On Wed, 8 Jun 2022 at 11:43, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Since it's 12.1 the use_concat is the necessary strategy.
> Andrew's comment about the parameters to the hint may be relevant, one for
> of the syntax options is
> /*+ use_concat [1|8] or_predicates( list of numbers ) */
>
> The list of numbers tells the optimizer which predicates concatenation
> should be used for. Unfortunately I think the numbering is based on
> normalising the where clause. I've got one note that says this is done by
> recursively numbering the predicates, and another not which says you number
> the predicates after generating the conjunctive normal form.
>
> To add further complications, once the or_predicates() has been set
> there's also an option for "predicate_reorders in the hint so you can end
> up with hints like:
> USE_CONCAT(_at_"MAIN" 8 OR_PREDICATES(4 20 48) PREDICATE_REORDERS((3 2) (2 3)
> (6 5) (5 6) (9 8) (8 9) (12 11) (11 12)))
>
> I don't have a definitive statement on when the number 1 or 8 is mandatory
> or required - but I have made some comments about that in
> https://jonathanlewis.wordpress.com/2020/08/19/subquery-with-or-3/
>
>
>
> Regards
> Jonathan Lewis
>
>
>
>
>
>
>
> On Wed, 8 Jun 2022 at 10:23, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
>> Thank Andy, Jonathan,
>>
>> THe version is 12.1. I proposed to rewrite the query but at this stage
>> due to various reasons it is complicated to have the code adjusted ( just
>> for the records I didn't mentioned DEV being lazy :D )
>>
>> The where clause is like this:
>> where
>> (T1.date < run_date and T2.date >=run_date and T2.date < run_date+1)
>> OR
>> (T1.date >= run_date-1 and T1.date < run_date and T2.date >=run_date -3
>> and T2.date < run_date)
>> and
>> (clause1 OR clause2)
>> AND
>> (clause3 OR clause4)
>> .....
>>
>> Basically in above pseudocode the T1 and T2 are both range
>> partitioned after date. If all this will be evaluated in a single go, T2
>> will be full partitioned scanned between run_date -3 and run_date+1 (4
>> partitions) and T1 will be accessed via NLs.
>>
>> If I rewrite the query (or making use of an oracle transformation)
>> replacing that OR condition with an UNION ALL the first union block will
>> have T2 scan just 1 partition and use NLs for accessing T1 and
>> second union block will have a nice hash join between one partition from T1
>> and 3 partitions from T2
>>
>> By using use_concat hint oracle will transform all the OR conditions and
>> I want basically just the first one, thus my initial quesiton
>>
>> THanks again for your comments!
>>
>>
>>
>>
>>
>>
>>
>>
>> În mie., 8 iun. 2022 la 10:23, Jonathan Lewis <jlewisoracle_at_gmail.com> a
>> scris:
>>
>>> Which version of Oracle? Do you really mean use_concat rather than
>>> or_expand?
>>> It might be safer to do a manual rewrite to Union All with a predicate
>>> that blocks accidental duplication
>>>
>>>
>>> Regards
>>> Jonathan Lewis
>>> (From my iPad mini; please excuse typos and auto-correct)
>>>
>>>
>>> On 8 Jun 2022, at 07:21, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
>>> wrote:
>>>
>>> Hello,
>>>
>>> For a query which has multiple OR conditions I want to use concatenation
>>> for just a particular OR condition and not all of them.
>>>
>>> Is this achievable in any way?
>>>
>>> Thank you.
>>>
>>>

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