How to reduce the use of tables

From: Amit Saroha <eramitsaroha_at_gmail.com>
Date: Wed, 20 Jan 2021 16:49:02 -0500
Message-ID: <CAG67e6QM8KosTH7NPGugAcdrknmJ17+q6myPK0hsy0pV-SYPSQ_at_mail.gmail.com>

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

Received on Wed Jan 20 2021 - 22:49:02 CET