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
