| Refresh snapshot data without logging (merged 3) [message #381891] |
Tue, 20 January 2009 04:13  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Materilaized views are normally used for summarized data access.
CREATE MATERIALIZED VIEW mv_snapshot_A
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 20/1440
WITH PRIMARY KEY
AS SELECT * FROM A;
This does not seem to be the case here as the materialized view seems to be just a full select.
The overhead of the snapshot logs are concerning for this core table.
Can we turn off logging in 10g ? the materialized view is defined as fast refresh/ build immediate .
The main requirement here is to keep the snapshot every 15 minutes so that the users can see the updated information ( the flow
of data from one location to other)
User get the locationwise count of data and can go further in details like in which location wise system wise data count.
As the base table is volatile the materialised view is used so that the moment the user clicks for locationwise
details the data is static for 15 min and user dont get confused.
Regards,
Oli
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Alternative for this materialised view [message #382190 is a reply to message #381891] |
Wed, 21 January 2009 05:00  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Is there any way to make refresh on base table?? I am trying to find alternatives for materialized view (with Fast refresh) for a select on table.
I tried with the below approach.
But, as Materilaized views are normally used for summarized data access I think its not a good idea to make a select on base table.It creates log while doing Fast refresh. I sthere a way to overcome this issue. Your advice on this issue will be highly appreciated. Please let me know if any more information is required.
CREATE MATERIALIZED VIEW mv_snapshot_A
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 20/1440
WITH PRIMARY KEY
AS SELECT * FROM A;
This does not seem to be the case here as the materialized view seems to be just a full select.
The overhead of the snapshot logs are concerning for this core table.
Can we turn off logging in 10g ? the materialized view is defined as fast refresh/ build immediate .
The main requirement here is to keep the snapshot every 15 minutes so that the users can see the updated information ( the flow
of data from one location to other)
Regards,
Oli
|
|
|
|
| Refresh snapshot data with nologging [message #382194 is a reply to message #381891] |
Wed, 21 January 2009 05:41 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
I want to make a snapshot of a base table and want to refresh the sanpshot after every 15 min with nologging instead of doing complete refresh using Materialised view. Can it be done?
Is there any way? Please help!
|
|
|
|
|
|
|
|
| Re: Refresh snapshot data with nologging [message #382205 is a reply to message #382203] |
Wed, 21 January 2009 06:25 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| Olivia wrote on Wed, 21 January 2009 12:16 | Please atleast respond if you have no answer to this!
|
I have no answer to this. Anyone else have no answer to this? C'mon folks, jump on!
Alternatively, Olivia, you could try putting in a little detail (as I'm sure you should know by now)
|
|
|
|
|
|
|
|
| Re: Refresh snapshot data with nologging [message #382213 is a reply to message #382208] |
Wed, 21 January 2009 06:52 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Olivia wrote on Wed, 21 January 2009 13:32 | Please find the link below where I posted my query and want to know if we can make a snapshot of a base table and want to refresh the sanpshot after every 15 min with nologging instead of doing complete refresh using Materialised view.
|
Nologging or without creating MV logs? What happened, when you tried it?
But, if it is the second case, Oracle documentation states it quite clearly: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2064161
| Quote: | FAST Clause
If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables.
|
Just think: how would you track all changes from last refresh without logging them? Do you think Oracle has some magic algorithm for obtaining this?
|
|
|
|
| Re: Refresh snapshot data with nologging [message #382214 is a reply to message #382209] |
Wed, 21 January 2009 06:52 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Because there was no answer to the question I asked. And I thought that the question should be asked in a different way. And I put the question.But I didn't get any solution to this.
I have tried my best to search for the solution and I atlast posted my question here thinking that someone would be here
who will give some possible solution/advice to me for some alternative.
Regards,
Oli
|
|
|
|
| Re: Refresh snapshot data with nologging [message #382219 is a reply to message #382213] |
Wed, 21 January 2009 06:59 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Thanks flyboy...I do understand that FAST refresh requires log to keep track.Without creating log I cant go for Fast refresh MVIEW...
Wanted to know
If there any way to make refresh on base table with NO LOG?? or MV without log?
In 10g I believe, if i am not wrong the complete refresh logic uses a delete/insert process, but it takes longer!!
I am trying to find alternatives for materialized view (with Fast refresh) for a select on table.
please advice
[Updated on: Wed, 21 January 2009 07:03] Report message to a moderator |
|
|
|
|
|
| Re: Refresh snapshot data without logging (merged 3) [message #382224 is a reply to message #381891] |
Wed, 21 January 2009 07:08 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Sorry Sir,
Which compells you to think that I was abusing.
Thanks flyboy...I do understand that FAST refresh requires log to keep track.Without creating log I cant go for Fast refresh MVIEW...
Wanted to know
If there any way to make refresh on base table with NO LOG?? or MV without log?
In 10g I believe, if i am not wrong the complete refresh logic uses a delete/insert process, but it takes longer!! Am I wrong?
Please reply..
I am trying to find alternatives for materialized view (with Fast refresh) for a select on table.
please advice
|
|
|
|
| Re: Refresh snapshot data without logging (merged 3) [message #382228 is a reply to message #381891] |
Wed, 21 January 2009 07:10 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
| Quote: |
Even if they aren't what you expected, it is NOT a reason to start a new one, not 2 new ones.
|
I asked in a new way thinking that my question need to be modified the way I was asking as the answer provided by you have no solution/ no alternatives.
Thanks
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
