This discussion is archived
2 Replies Latest reply: Feb 15, 2013 11:43 AM by rp0428 RSS

Materialized Views.

934896 Newbie
Currently Being Moderated
Hi

I am new to materialized views, and I was trying to read some documentation and it got confusing, I have a requirement to create a materialized view in schema targ on table s in sour schema and I want it to refresh every 20 minutes. I also want a materialized view log?

I need help with what the syntax would be for the materialized view and materialized view log. AND also where would the materialized view log be created(what schema).


Thanks
  • 1. Re: Materialized Views.
    Justin Cave Oracle ACE
    Currently Being Moderated
    Are these schemas in the same database? You don't mention multiple databases. You wouldn't use a materialized view to replicate data from one schema to another. You might want to aggregate data from a table in one schema in a materialized view in a different schema but that would be pretty odd-- you'd generally want the aggregated data in the same schema that the detail data is in.

    Justin
  • 2. Re: Materialized Views.
    rp0428 Guru
    Currently Being Moderated
    >
    I am new to materialized views, and I was trying to read some documentation and it got confusing, I have a requirement to create a materialized view in schema targ on table s in sour schema and I want it to refresh every 20 minutes. I also want a materialized view log?
    >
    It you want the MV to refresh every 20 minutes you use the START WITH and NEXT clauses
    >
    START WITH Clause

    Specify a datetime expression for the first automatic refresh time.

    NEXT Clause

    Specify a datetime expression for calculating the interval between automatic refreshes.

    Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then the database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then the database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the create_mv_refresh entirely, then the database does not automatically refresh the materialized view.
    . . .
    Creating Primary Key Materialized Views: Example The following statement creates the primary key materialized view catalog on the sample table oe.product_information:

    CREATE MATERIALIZED VIEW catalog
    REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/4096
    WITH PRIMARY KEY
    AS SELECT * FROM product_information;
    >
    You would use 1/(24 * 3) = 1/72 for 1/3 hour (20 min.)
    >
    I need help with what the syntax would be for the materialized view and materialized view log. AND also where would the materialized view log be created(what schema).
    >
    Have you tried reviewing/creating the examples in the SQL Language doc?
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6002.htm#i2106785

Legend

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