Re: use_concat question

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