Jump to content
p_sony_2000@yahoo.com

Complete refresh of MV (creation with start with and next clause) taking long time for 5 million rows

Recommended Posts

Hi,

I have a materialized view created along start with and next clause for a complete refresh. The complete refresh takes 1+ hour for 5million rows. I want to change the refresh to non-atomic with the atomic-refresh = False. Can this be achieved in the create MV statement or do we need a separate job.

Also tried creating the MV as BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY and then refresh using DBMS_MVIEW.REFRESH (MV_NAME,
                       METHOD           => 'C',
                       ATOMIC_REFRESH   => FALSE);

But here also the refresh takes 1 hour.

 

 

Share this post


Link to post
Share on other sites

Hi, and welcome to the forum!

 

First, separate the query from the create materialized view statement.  If the query takes the most time, try using parallel query to increase build time.

 

Here are my notes on creating a materialized view with the atomic clause:

http://www.dba-oracle.com/t_materialized_view_atomic_refresh.htm

 

Hope this helps.  Good luck!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×