Collecting Annual Aggregated Data For Later Quick Access

I have a number of sql queries which take year as a parameter and generate various annual reports for the given year. Those queries are quite cumbersome and take a considerable amo

Solution 1:

A materialized view would be a great option for what you are looking to do. This way you can write the query once for the view, then have the data in the materialized view refresh as often as you'd like. You can have a job that refreshes the data once per night, on the weekends, or whatever frequency you choose.

After the materialized view is created, you can also add indexes on top of the materialized view to assist with query performance if you so choose.

A quick example on how to create a materialized view can be seen below.

CREATETABLE sale
(
    product_id     NUMBER,
    sale_date      DATE,
    sale_amount    NUMBER
);

INSERTINTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE'2019-02-01', 40.25);

INSERTINTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE'2019-02-01', 80.99);

INSERTINTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE'2020-02-01', 30.50);

INSERTINTO sale (product_id, sale_date, sale_amount)
     VALUES (124, DATE'2020-02-01', 46.75);

CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ASSELECT product_id,
             SUM (sale_amount)                 AS annual_sales,
             MAX (sale_amount)                 AS max_price,
             MIN (sale_amount)                 AS min_price,
             EXTRACT (YEARFROM sale_date)     ASyearFROM sale
    GROUPBY product_id, EXTRACT (YEARFROM sale_date);

Result

select*from sales_summary;

   PRODUCT_ID    ANNUAL_SALES    MAX_PRICE    MIN_PRICE    YEAR
_____________ _______________ ____________ ____________ _______
          124121.2480.9940.25201912477.2546.7530.52020