This discussion is archived
4 Replies Latest reply: Oct 11, 2013 1:15 AM by Igor S. RSS

SQL Query - store the result for optimization?

Igor S. Newbie
Currently Being Moderated

Good day experts,

 

I am looking for advice on a report. I did a lot of analytic functions to get core data that I need to make my report and its takes around 50 min for SQL to complete. Now with this data I need to create 3 different reports and I cant use the same SQL since there is a lot of agregation (example would be group by product in one case and by client in 2nd). For each of those different group bys I need a different report.

 

So how to create 3 reports from 1 SQL query without running the query 3 times?

 

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.

 

Best regards,

Igor

  • 1. Re: SQL Query - store the result for optimization?
    SomeoneElse Guru
    Currently Being Moderated

    Perhaps a Materialized View?

  • 2. Re: SQL Query - store the result for optimization?
    Mike Kutz Expert
    Currently Being Moderated

    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

     

     

    IgorS. wrote:

     

    (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.

    eg

    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);

     

     

    MK

  • 3. Re: SQL Query - store the result for optimization?
    rp0428 Guru
    Currently Being Moderated
    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.

  • 4. Re: SQL Query - store the result for optimization?
    Igor S. Newbie
    Currently Being Moderated

    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!

     

    Best regards,

    Igor

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points