Re: How to reduce the use of tables

If i'm not mistaken, your last SELECT should be logical equivalent to

select --4th union t1

        join (select cd
                from (select id, c2, cd, max(id) over() max_id
                        from t2
                       where cd in ('IF86', 'IF87', 'IF88', 'IF88'))
               where id = max_id
               group by cd) t2 on (t2.c2 = to_char(t1.c2))

Regards

Maxim

On Wed, Jan 20, 2021 at 10:49 PM Amit Saroha <eramitsaroha_at_gmail.com> wrote:

> Hi Listers,
>
> I have the below statement which is used 4 times with different values of
> column cd of table t2 in 4 different UNION queries. Table t2 in size is
> over 50 GBs having 100 different CDs data and in my query, it's used 6
> times.
>
> SELECT c1 FROM t1
> WHERE EXISTS (
> SELECT NULL
> FROM t2 t2
> WHERE t2.c2 = to_char (t1.c2)
> AND t2.cd = 'IF86'
> AND t2.id = ( SELECT MAX (id)
> FROM t2 t2
> WHERE t1.cd = t2.cd))
>
> I am planning to LIST Partition the table in column CD to reduce the FTS
> but I think there should be a better way to write it only once by using IN
> clause on CD column for for CDs values like below and reduce the 6 FTS of
> table t2 to only 2. However, I am looking for you expert suggestions to
> rewrite the query and use only 1 FTS of table t2.
>
> Please suggest if there's a way to rewrite the above query and use table
> t2 only once.
>
> SELECT * FROM
> (SELECT -- 1st union
> UNION ALL
> SELECT -- 2nd union
> UNION ALL
> SELECT -- 3rd union
> UNION ALL
> SELECT -- 4th union) t1
> WHERE EXISTS (
> SELECT NULL
> FROM t2 t2
> WHERE t2.c2 = to_char (t1.c2)
> AND t2.cd IN ('IF86', 'IF87', 'IF88', 'IF88')
> AND t2.id = ( SELECT MAX (id)
> FROM t2 t2
> WHERE t1.cd = t2.cd))
>
>
> Best Regards,
> Amit
>

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