Schedule Oracle to run a query and save that data into a table

Question

Is there a way I can schedule Oracle to run a query and save that data into a table?

Or are there ways I can schedule to execute a Oracle query to run by itself at a time and save the data into a table?

I have an automated reporting page that has a performance issue due to heavy logic executing on a wide range of time (covering 2 years of data + logic calculations). If clients click on the reporting page they are complaining about long loading times; by this dumping data to a table at a current time could solve my problem.

Search for `DBMS_SCHEDULER` package. – Rachcha Apr 02 '13 at 06:30 — Rachcha, Apr 02 '13 at 06:30

score 5 · Answer 1 · answered Apr 02 '13 at 06:31

You can create a materialized view

CREATE MATERIALIZED VIEW mv_name
  REFRESH COMPLETE
  START WITH sysdate
  NEXT sysdate + interval '1' hour
AS
<<your query here>>

This will create a materialized view that refreshes itself every hour. The materialized view stores the data in a table segment that is also named MV_NAME which you can query just like any other table.

Depending on the query, you may be able to do an incremental refresh rather than a complete refresh by creating appropriate materialized view logs on the base tables.

Thank you so much for your quick response Sir Justin! This might solve my problem, I will try it and update you. Thank you very much again! :) — Demonic_Dog, Apr 02 '13 at 06:55

Schedule Oracle to run a query and save that data into a table

1 Answers1