Re: Big Complex MV creation and Refresh

From: Mladen Gogala <>
Date: Fri, 13 Apr 2018 19:32:19 -0400
Message-ID: <>

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

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

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

Received on Sat Apr 14 2018 - 01:32:19 CEST

Original text of this message