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@xxxxxxxxx> 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
