1 person found this helpful
Perhaps a Materialized View?
1 person found this helpful
MV was the first thing that came to mind.
If you have variables in a WHERE clause, a Global Temporary Table would probably be better
(example would be group by product in one case and by client in 2nd).
HINT: GROUP BY GROUPING SETS ( product, client)
This uses one SELECT statement but two different GROUP BY results.
select job, deptno, sum(sal) sal_total ,grouping_id( job ) is_grouped_by_job -- helps identify which GROUP BY is used from scott.emp group by grouping sets (job,deptno);
So how to create 3 reports from 1 SQL query without running the query 3 times?
You already know the obvious answer - store the data 'somewhere'.
The appropriate 'somewhere' depends on your actual business requirements and you did not provide ALL of them.
MV - if the query is always the same you could use an MV and do a complete refresh when you want new data. The data is permanent and can be queried by other sessions but the query that accesses the data will be frozen into the MV definition.
GTT (global temp table) - if a NEW data load AND the three reports will ALWAYS be executed by a single session and then the data is NOT needed anymore then a GTT can work. The query that loads the GTT can be different for each run but the data will only be available for a single session and ONLY for the life of that session. So if anything goes wrong and the session terminates the data is gone.
First thing that comes to mind is to store the result set into a dummy table and then query the table since the core data I get is around 300 rows and then do different group bys.
That is commonly referred to as a 'REPORT-READY table'. Those are useful when the data needs to be permanent and available to multiple sessions/users. Typically there is a batch process (e.g. package procedure) that periodically refreshes/updates the data during an outage window. Or the table can have a column (e.g. AS_OF) that lets it contain multiple sets of data and the update process leaves existing data alone and creates a new set of data.
If your core data is around 300 rows you may want to consider a report-ready table and even using it to contain multiple sets of data. Then the reports can be written to query the data using an AS_OF value that rolls up and returns the proper data. You don't need an outage window since older data is always available (but can be deleted when you no longer need it.
If you only need one set of data you could use a partitioned work table (with only one partition) to gather the new set of data and then an EXCHANGE PARTITION to 'swap' in the new data. That 'exchange' only takes a fraction of a second and avoids an outage window. Once the swap is done any user query will get the new data.
Yeah that is exactly what I need. Place to hold my result to use different aggregations on. I went with GTT since I need to use "Rollups" and a lot of group bys. I never tried to do it using "with as" might look into it.
Thank you all for your advices!