Materialized view query

[desi_bob02]

Hi all,

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
NAME LAST_REFRESH
------------------------------ -----------------
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,
regards,

[Tuma]

Hi all,

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
NAME LAST_REFRESH
------------------------------ -----------------
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,
regards,

if you know the refresh job stats at 3:00 then you can do this and see the time it finished.
SQL> alter session set nls_date_format ='DD-MM-YYYY hh24:mi:ss';

Session altered.

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.

[PAVB]

I have MVs without any index on them and they are great.

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.

Answering posters question, in this particular scenario indexes have to be created as part of MV "refresh" process.