Re: use_concat question

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