Oracle Materialized Views – Fast Refresh

1240 pts.
Tags:
Oracle
Hi, Does anyone know a good how-to guide that covers materialized views in Oracle? My immediate problem is that I can't create a materialized view that is a subset of a single table (selected rows and columns) that refreshes as soon as changes to the base table are committed. Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Prerequisites:

grant query rewrite to <user_creating_mv>

as SYSDBA

alter session set query_rewrite_enabled =true;

alter session set query_rewrite_integrity=enforced;

Say your base table is table_a with a primary key.

You create a materialized view log on table_a

create materialized view log on table_a;

Now to create a materialized view that will refresh when ever the table is changed

you execute the following statement

CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW
BUILD IMMEDIATE
REFRESH ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT <what ever you want to select>
FROM <the single base table?
GROUP BY <IF APPLICABLE>
/

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FrankKulash
    Thanks! That works in Oracle 9.2, but in 8.1.7 I get "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view" Here's my code: -- **************************************** ALTER SESSION SET query_rewrite_enabled = TRUE; ALTER SESSION SET query_rewrite_integrity = ENFORCED; DROP MATERIALIZED VIEW emp_view; SELECT * FROM user_sys_privs; CREATE MATERIALIZED VIEW LOG ON emp; CREATE MATERIALIZED VIEW emp_view BUILD IMMEDIATE REFRESH ON COMMIT ENABLE QUERY REWRITE AS SELECT empno , ename , deptno , mgr FROM emp WHERE deptno 10 ;
    1,240 pointsBadges:
    report
  • Azimfahmi
    Try creating an updateable materialized view in the same schema.. Use insert, update, delete trigger on the base table to appropriately update the materialized view; in that case you do not want the log on the materialized view. If this seems an unacceptable solution, you may want to provide a fast refresh time (NEXT) on your CREATE MATERIALIZED VIEW script.. But then you have to be content with the fact that the materialized view data is not real time data and that it lags behind the base table by your specified refresh rate. Let me know what you think. Thanks Azim
    585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following