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:
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
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