RE: How to reduce the use of tables
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
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-lReceived on Thu Jan 21 2021 - 15:48:37 CET