OraFAQ Forum: SQL & PL/SQL » Materialized view

Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view (11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized view [message #618854]

Wed, 16 July 2014 03:06

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

I need to create the materialised view based on multiple tables (these tables are very big tables,nearly 2 billion records will be there).
So i need to create materilaised view on incremental basis..which one is better whether FAST REFRESH or ON COMMIT...which one is better performance?

[EDITED by LF: fixed topic title typo]

[Updated on: Wed, 16 July 2014 14:28] by Moderator

Report message to a moderator

Re: MAterialised view [message #618855 is a reply to message #618854]

Wed, 16 July 2014 03:19

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

You can't create an MV incrementally. ON COMMIT or FAST refers to the refresh mechanism, not to the creation.

Report message to a moderator

Re: MAterialised view [message #618857 is a reply to message #618855]

Wed, 16 July 2014 03:23

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi John,

I need to update the MV for the latest updates on big tables(joins will be there).which option is better in performance point.
i don't have idea on "ON COMMIT" & "FAST" how they willl work ...??could you pls help on this

Report message to a moderator

Re: MAterialised view [message #618858 is a reply to message #618857]

Wed, 16 July 2014 03:28

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Have you read this yet?
http://docs.oracle.com/cd/E16655_01/server.121/e17749/refresh.htm
If anything isn't clear, explain the problem. Do not forget to give the paragraph reference.

Report message to a moderator

Re: MAterialised view [message #618863 is a reply to message #618858]

Wed, 16 July 2014 03:54

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi John,

Thanks for your reply..i have read your document.Still i have dout.which one is better "ON COMMIT" OR "FAST" refresh.i read that FAST refresh needs the MV LOG.So it may be degrade the database performance.?because i am using 5 big tables which are having billion records ...

Report message to a moderator

Re: MAterialised view [message #618874 is a reply to message #618863]

Wed, 16 July 2014 04:32

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The overload for MV logs does not depend on the size of the tables but on the DML operations on them.

Report message to a moderator

Re: MAterialised view [message #618886 is a reply to message #618863]

Wed, 16 July 2014 05:12

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

mvrkr44 wrote on Wed, 16 July 2014 09:54

hi John,

Thanks for your reply..i have read your document.Still i have dout.which one is better "ON COMMIT" OR "FAST" refresh.i read that FAST refresh needs the MV LOG.So it may be degrade the database performance.?because i am using 5 big tables which are having billion records ...

So what did you think of the paragraph that specifically mentions a disadvantage of ON COMMIT refresh?"

Report message to a moderator

Re: MAterialised view [message #618923 is a reply to message #618863]

Wed, 16 July 2014 09:50

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

mvrkr44 wrote on Wed, 16 July 2014 04:54

Still i have dout.which one is better "ON COMMIT" OR "FAST" refresh.

They have nothing in common. "ON COMMIT" indicates when and "FAST" indicates how. And if you think fast might degrade performance, think why Oracle named that option as fast Laughing

. The only time where fast vs complete has to be investigated is when large percentage of source table rows are modified between refreshes.

SY.

[Updated on: Wed, 16 July 2014 09:52]

Report message to a moderator

Re: MAterialised view [message #618924 is a reply to message #618923]

Wed, 16 July 2014 09:57

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi,

But FAST will work on MV Logs..so we need to maintain a another Job to remove the old logs ???
if we keep that logs any performance impact will be there ?

Report message to a moderator

Re: MAterialised view [message #618930 is a reply to message #618924]

Wed, 16 July 2014 10:26

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

so we need to maintain a another Job to remove the old logs ???

No this is done automatically.

Quote:

if we keep that logs any performance impact will be there ?

Far less than to refresh completely.

Report message to a moderator

Previous Topic:	Diffrent Number and type of parameter passing
Next Topic:	Dbms_Parallel_Execute.Run_Task gets stuck

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:54:38 CDT 2024