Re: Suggestion for application caching

On 8/18/23 17:21, yudhi s wrote:
>
> This query has five "UNION ALL" conditions and the TEMP SPACE is
> spilling while applying the DISTINCT clause mostly. But the
> application team is resisting that they can't get rid of the
> "DISTINCT" clause because of current design and also the UNION ALL is
> from multiple tables with different Join criteria. Also only the
> specific columns are exposed out of the query but not all. Is there
> any other way to minimize the tempspill here?
>
That means that your model could use some improvement. Using DISTINCT is usually a consequence of missing objects. Furthermore, it would probably be possible to de-normalize the structures and avoid UNION ALL stuff. Sending 20GB to your app servers will probably kill your TEMP space and your network. In other words, what you need is not caching, what you need is a dose of Ralph Kimball. Look for "Data Warehouse Toolkit" by Ralph Kimball on Amazon.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l