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@xxxxxxxxxxxxx>
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
