Materialized view query
how to check the time taken for materialized view to execute at one time.
My materialized view is a complete refresh which automatically executes at 3:00.
a) I have queried dba_jobs and dba_mview_refresh_times, and matched the last_date and last_refresh to find the total time. How can I be know that JOB 80 corresponds to my view MVIEW_UNION in a much easier way?
SQL> select job,last_date,total_time from dba_jobs order by last_date;
JOB LAST_DATE TOTAL_TIME
---------- ----------------- ----------
80 05/05/10 03:00:02 5255
SQL>select name,last_refresh from dba_mview_refresh_times where owner
MVIEW_UNION 05/05/10 03:00:02
b) Also is total_time the time it took the query to execute today at 3:00, i.e it started at 3:00 and ended at around 4:45(3+5255 msec) or is it the overall time it has taken since the day it was scheduled in dba_jobs( i.e 1 week back)
c) Also my source table dosent contain any indexes and I have created indexes on materialized view, i want to know if the complete refresh will recreate the indxes.
thanks in advance,
if you know the refresh job stats at 3:00 then you can do this and see the time it finished.
Originally Posted by desi_bob02
SQL> alter session set nls_date_format ='DD-MM-YYYY hh24:mi:ss';
SQL> select last_refresh_date,mview_name from dba/user_mviews;
I think you are better off creating the indexes on your base table rather than your MV if you had only one choice...because your MV refresh is a select against your table just as any user query...
I have MVs without any index on them and they are great.
Looking for the greatest evil in the world? Look in the mirror.
It all depends on the size of the MV - we do have small MVs doing great with no indexes but some large MVs are certainly benefiting of having a good indexing strategy in place.
Originally Posted by Tuma
Answering posters question, in this particular scenario indexes have to be created as part of MV "refresh" process.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width