RE: How to reduce the use of tables

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 21 Jan 2021 09:48:37 -0500
Message-ID: <1fce01d6f004$80c13750$8243a5f0$_at_rsiz.com>



Without formulating the final sql for you, I believe you end up with one row having the max(id) for a cd for each cd, meaning 4 rows.  

So if you build that table either as a gtt or in a with clause, then you get a single partition scan for each cd value to dynamically construct that rowsource in your query.  

(If you build a local index on each partition with id, then that could be a max of index row source for each partition, but there may be other side effects for doing that. That becomes a which is more important goal seeking task with an evaluation of what [inserts, at least a little bit, for sure] gets slower due to having the local index. The index and partitioning might even make lots of queries faster, but it might destabilize something important too, for example the CBO deciding the index has a great cluster factor but unfortunately then reading large swaths of a partition via index instead of via partition scan. You can fix THAT too, but it might be human piecework to do so if you can’t figure out how to tweak stats and hints to get the right thing. The CBO might also get everything BETTER having the local index on id, but that depends on too many unknowns for me to guess).
 

I’d probably give the with or gtt creation bit nomerge and materialize hints, since the shape of what you have described screams for that to be done absolutely first and once.  

Then use that single block in-memory construction’s name as the row source for each of your subqueries, no longer giving a rat’s ass how many times you scan the four rows in that single memory block.

Notice that you DON’T need the “fifth” row for the set of all four, since the high id for each is already in your 4 row “scratchpad.” (intentional nod to JL).  

Good luck.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Amit Saroha Sent: Wednesday, January 20, 2021 4:49 PM To: ORACLE-L (oracle-l_at_freelists.org) Subject: How to reduce the use of tables  

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 Thu Jan 21 2021 - 15:48:37 CET

Original text of this message