1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to Create Materialized View that auto-refreshes after a Period of time

Discussion in 'SQL PL/SQL' started by pizzarostone, Feb 28, 2012.

  1. pizzarostone

    pizzarostone Guest

    how do i create a materialized view that refreshes every midnight from a table with millions of rows,
    which has thousands of new data being inserted every. this materialized view is needed for report purposes so as
    not to strain the main tables with the heavy queries the reports will be generating.
    i really need this to be done as my boss has a gun in my mouth ready to fire! never with materialized views.
  2. zargon

    zargon Community Moderator Forum Guru

    Likes Received:
    Trophy Points:
    Aurora, CO
    This is a fairly straightforward task that needs some preparation before you build the materializEd view. The base table needs to have a materialized view log built. A basic example of how to do this starting with table creation:

    Code (SQL):
    SQL> CREATE TABLE yarpno(
    2 sming NUMBER,
    3 alroo varchar2(15),
    4 repnart varchar2(12),
    5 CONSTRAINT yarpno_pk
    6 PRIMARY KEY(sming)
    7 );
    TABLE created.
    SQL> CREATE materialized VIEW log ON yarpno WITH PRIMARY KEY;
    Materialized VIEW log created.
    2 FOR i IN 1..500 loop
    3 INSERT INTO yarpno
    4 VALUES (i, 'Smeezo '||i, 'Twe'||i);
    5 END loop;
    7 commit;
    9 END;
    10 /
    PL/SQL PROCEDURE successfully completed.
    SQL> --
    SQL> -- Be 'lazy' and use 'select * From ...'
    SQL> --
    SQL> CREATE materialized VIEW yarpno_mv
    2 refresh fast
    3 NEXT trunc(sysdate+1)
    4 AS SELECT * FROM yarpno;
    Materialized VIEW created.
    The materialized view created will refresh every day at midnight; the fast refresh keeps the materialized view from being completely repopulated with each refresh; the materialized view log enables the fast refresh option. Notice also that the way the materialized view was created is considered a 'lazy' way (using 'select *') -- it is better for all concerned to list the columns for the materialized view in the select statement used to build it.

    If you have any questions do not hesitate to ask.
  3. tj.abrahamsen

    tj.abrahamsen Active Member

    Likes Received:
    Trophy Points:
    What about creating a scheduled job using DBMS_SCHEDULER.CREATE_JOB and have it run a procedure that calls the following command: "DBMS_MVIEW.refresh('<name_of_mv>');"?

    I have not used MVs so much, but I have scheduled a lot of jobs.

    One benefit using a scheduled job is that you can see the log using "select * from user_scheduler_jobs;". If for some reason something goes wrong, and your DBA does not let you know...you can get some info using this query "select * from user_scheduler_job_run_details;"

    This way you can easily change the scheduled job to set a different frequency, and you don't have to touch (read: recreate) the MV.

    Just a thought.

    ~ TJ