** select vs CTAS vs MV create

Hi, 
I am doing a select involving multiple tables and it returns 20000 rows within 
5 minutes. Create table .... as the select (CTAS) finishes within 2 minutes. 
However when I try create MV (refresh force ) on the same select it does not 
finish even in three hours. Some of the tables are remote accessed thru db 
link. For select and CTAS I see some v$session_longops that show elapsed 
time/remaining like around 100 seconds. With create MV it is like 4000. For the 
same full table scan with same number of blocks. Next step is to look for 
10046. I will try MV on prebuilt table. Does anyone have a clue why so much 
diff between select/CTAS and Create mv. For select I can get the explain plan. 
For create I cannot. Is there a way to get the plan for create MV.  Thanks for 
help. 
       
---------------------------------
Never miss a thing.   Make Yahoo your homepage.

Other related posts: