** select vs CTAS vs MV create
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 21 Dec 2007 14:08:17 -0800 (PST)
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:
- » ** select vs CTAS vs MV create
