Re: Big Complex MV creation and Refresh

Have you tried using DBMS_MVIEW.EXPLAIN_MVIEW? Here are three pages which will explain you how to use that procedure:

https://community.toadworld.com/platforms/oracle/w/wiki/2784.dbms-mview-explain-mview

http://www.orafaq.com/node/831

http://oracle-datawarehousing.blogspot.com/2011/02/analyzing-materialized-view.html

There is also a good material about that in Christian Antognini's book, which I wholeheartedly recommend.
Regards

On 04/13/2018 06:00 PM, Sanjay Mishra (Redacted sender smishra_97 for DMARC) wrote:
> Question is
> 1. If I use create MV with parallel  and select * from View; will be
> paralleling. I  used this method and it took 16hr to refresh. Not sure
> how I can more advantage with Parallel Query to speed up the creation
> and there after Refresh. Is following will help where View is based on
> multiple tables and not sure Paralle Select from view can help
>   Original Def
>     create Materialized view test parallel 20 refresh with rowid as
> select * from test_view;
>   Or this will be better
>     create Materialized view test parallel 20 refresh with rowid as
> select /*+parallel(10) */* from test_view;
>   Or I had to change the view defintion to also include Parallel in
> all Select in it to
>
> Trying to understand as how Parallel Query can help. None of the table
> involved in the view are partitioned.
>
> 2. Checking few sites and came to know Refresh will not be Parallel
> unless all select has parallel in it. As otherwise refresh will be serial.
>
>
> thanks for suggestion and any reference
>
> Sanjay

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l