Materialized View [message #464233] |
Wed, 07 July 2010 01:17 |
abdulahads
Messages: 44 Registered: October 2009 Location: SAUDI
|
Member |
|
|
Please can any one give me a simple example to
Create Materialized view with Refresh on commit example.
with create Log file example.
Thanks in advance
|
|
|
|
|
|
Re: Materialized View [message #464248 is a reply to message #464242] |
Wed, 07 July 2010 02:08 |
|
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
Good Question!
IMO not required,Because the log uses to refresh the view for the base table changes.
If we use "REFRESH ON COMMIT" the same refresh happend automatically without a log.
And seniors let me know If I am missing anyinfo here.
Thank you
sriram
[Updated on: Wed, 07 July 2010 03:43] Report message to a moderator
|
|
|
Re: Materialized View [message #464250 is a reply to message #464247] |
Wed, 07 July 2010 02:18 |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
Its_me_ved wrote on Wed, 07 July 2010 02:05Yes.ON COMMIT MVIEW logs must be built as ROWID logs, not as primary-key logs.
Regards
Ved
My apologies I was wrong
SQL> create materialized view mv2
2 REFRESH ON COMMIT
3 as select col from g501;
Materialized view created.
|
|
|
Re: Materialized View [message #464270 is a reply to message #464250] |
Wed, 07 July 2010 03:37 |
John Watson
Messages: 8939 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
No log needed:jw> create table t1 as select * from all_users;
Table created.
jw> desc t1
Name Null? Type
----------------------------------------------------- -------- -------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
jw> alter table t1 add constraint t1pk primary key(user_id);
Table altered.
jw> create maerialized view mv1 refresh on commit as select * from t1;
create maerialized view mv1 refresh on commit as select * from t1
*
ERROR at line 1:
ORA-00901: invalid CREATE command
jw> create materialized view mv1 refresh on commit as select * from t1;
Materialized view created.
jw> selectcount(*) from mv1;
SP2-0734: unknown command beginning "selectcoun..." - rest of line ignored.
jw> select count(*) from mv1;
COUNT(*)
----------
44
jw> delete from t1;
44 rows deleted.
jw> select count(*) from mv1;
COUNT(*)
----------
44
jw> commit;
Commit complete.
jw> select count(*) from mv1;
COUNT(*)
----------
0
jw>
|
|
|