Home » Server Options » Replication » Refresh snapshot data without logging (merged 3)
Refresh snapshot data without logging (merged 3) [message #381891] Tue, 20 January 2009 04:13 Go to next message
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
Re: Materialised View with no logging for Fast REFRESH [message #381898 is a reply to message #381891] Tue, 20 January 2009 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FAST REFRESH => MVIEW LOG

No way to get the former without the latter.

Regards
Michel
Re: Materialized view with no log for fast refresh? (merged) [message #381902 is a reply to message #381891] Tue, 20 January 2009 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't crosspost your question.
You SHOULD know it now.

Regards
Michel
Re: Materialized view with no log for fast refresh? (merged) [message #381904 is a reply to message #381902] Tue, 20 January 2009 04:24 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sorry for being multi posted. It happened due to network probem.


Regards,
Oli
Re: Materialised View with no logging for Fast REFRESH [message #381905 is a reply to message #381898] Tue, 20 January 2009 04:26 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Any alternative to handle this issue? if there is a better way to refresh the mat. view pl let us know


Regards,
Oli

[Updated on: Tue, 20 January 2009 04:36]

Report message to a moderator

Re: Materialized view with no log for fast refresh? (merged) [message #381922 is a reply to message #381891] Tue, 20 January 2009 05:22 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Is there any alternative?
Alternative for this materialised view [message #382190 is a reply to message #381891] Wed, 21 January 2009 05:00 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #382203 is a reply to message #382194] Wed, 21 January 2009 06:16 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Please atleast respond if you have no answer to this!
Re: Refresh snapshot data with nologging [message #382204 is a reply to message #382203] Wed, 21 January 2009 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's been 25 minutes, and it's lunchtime here in the UK/Europe

Learn some patience.
Re: Refresh snapshot data with nologging [message #382205 is a reply to message #382203] Wed, 21 January 2009 06:25 Go to previous messageGo to next message
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 #382208 is a reply to message #382205] Wed, 21 January 2009 06:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the reply.

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.


http://www.orafaq.com/forum/t/139317/125380/





Regards,
Oli
Re: Refresh snapshot data with nologging [message #382209 is a reply to message #382208] Wed, 21 January 2009 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So why did you create a new topic for the same question?
For me, this behaviour means the end of my collaboration on this.
And topics are merged.

Regards
Michel
Re: Refresh snapshot data with nologging [message #382213 is a reply to message #382208] Wed, 21 January 2009 06:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 with nologging [message #382220 is a reply to message #382214] Wed, 21 January 2009 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You started a topic for this.
You got answers for this.
Even if they aren't what you expected, it is NOT a reason to start a new one, not 2 new ones.
And even more moaning for answer after half an hour.
This disgust me. /forum/fa/4073/0/

I you want more pick up your phone and hire a consultant but do not abuse of our willingness to help people.

Regards
Michel
Re: Refresh snapshot data without logging (merged 3) [message #382224 is a reply to message #381891] Wed, 21 January 2009 07:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Refresh snapshot data without logging (merged 3) [message #382229 is a reply to message #382224] Wed, 21 January 2009 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If there any way to make refresh on base table with NO LOG?? or MV without log?

Yes, complete refresh.

Quote:
if i am not wrong the complete refresh logic uses a delete/insert process

Wrong, it can use truncate if you say so.

Regards
Michel

[Updated on: Wed, 21 January 2009 07:26]

Report message to a moderator

Re: Refresh snapshot data without logging (merged 3) [message #382232 is a reply to message #382229] Wed, 21 January 2009 07:27 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for your reply. But if we use complete refresh is not there a possibility of getiing no data at a certain point of time?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:616795500346622064
Re: Refresh snapshot data without logging (merged 3) [message #382233 is a reply to message #382232] Wed, 21 January 2009 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No unless you tell Oracle to use truncate.
You have 2 ways:
- fast, truncate, risky
- slow, transactional (delete, insert), safe

Choose the one you want.

Regards
Michel

[Updated on: Wed, 21 January 2009 07:33]

Report message to a moderator

Re: Refresh snapshot data without logging (merged 3) [message #382235 is a reply to message #382233] Wed, 21 January 2009 07:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Not getting you here..


Quote:

- fast, truncate, risky
- slow, transactional (delete, insert), safe




Both option available in complete refresh?
Re: Refresh snapshot data without logging (merged 3) [message #382239 is a reply to message #382235] Wed, 21 January 2009 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
It is the first word and sentence of Tom's answer in the link you posted.

Regards
Michel
Re: Refresh snapshot data without logging (merged 3) [message #382241 is a reply to message #382239] Wed, 21 January 2009 08:38 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
slow, transactional (delete, insert), safe will create log I believe.

So,if this is the case then is FAST REFRESH not better approach?
Re: Refresh snapshot data without logging (merged 3) [message #382244 is a reply to message #382241] Wed, 21 January 2009 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
slow, transactional (delete, insert), safe will create log I believe.

What kind of logs are you talking about? mview log or redo log?

Regards
Michel
Re: Refresh snapshot data without logging (merged 3) [message #382245 is a reply to message #382244] Wed, 21 January 2009 08:50 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
redo log...if log is there transaction is slow right?
Re: Refresh snapshot data without logging (merged 3) [message #382246 is a reply to message #381891] Wed, 21 January 2009 08:53 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
If we compare slow, transactional (delete, insert) for COMPLETE REFRESH with FAST REFRESH here? We can go with FAST refresh as both creates log?
Re: Refresh snapshot data without logging (merged 3) [message #382247 is a reply to message #382245] Wed, 21 January 2009 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You never talked about mview logs in these topics?

Regards
Michel
Re: Refresh snapshot data without logging (merged 3) [message #382249 is a reply to message #382247] Wed, 21 January 2009 09:08 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
mview log records changes to the table so that the Mview can retrieve the data quicker.

In Fast refresh, what I know is both undo/redo log is maintained.And you cant create mview with Fast refresh without mview log.


Thanks


Re: Refresh snapshot data without logging (merged 3) [message #382423 is a reply to message #381891] Thu, 22 January 2009 04:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
no reply!
Re: Refresh snapshot data without logging (merged 3) [message #382429 is a reply to message #382423] Thu, 22 January 2009 04:48 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there a question in your last post?
There was in mine!

Regards
Michel
Previous Topic: ORA-06502 in DBMS_RECTIFIER_DIFF.DIFFERENCES
Next Topic: MVs associated with a MV LOG
Goto Forum:
  


Current Time: Fri Mar 29 00:17:25 CDT 2024