Re: Big Complex MV creation and Refresh

Well, if you really want to use parallel,I think you put the parallel hint in the TEST_VIEW definition.

Also on your refresh you can set atomic=false but that truncates the MVIEW which is great as long as no one is using it. If people are still going to be using it, don't use atomic=false. (Normal MVIEW refreshes do deletes and inserts which is very expensive when you don't have materialized view logs available).

Chris

On Fri, Apr 13, 2018 at 5:00 PM, Sanjay Mishra <dmarc-noreply_at_freelists.org> wrote:

> Hi
>
> I had a View which is Select from multiple Tables and joins. Selecting
> data from it take lots of time and been used extensively. I am planning to
> create MV on it as Data from View is only changed once a week. There are
> time when this view is not accessed and so can use the timeframe.
>
> 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
>

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