Thank you for your response.

We did think about the suggested solution but rejected it because:
1. The dependency on job_queue_interval. There is a small time interval before the refresh. In that interval MV data is not correct.

2. The fact that the master tables are almost static (insert/update takes place only a few times a year) which doesn't warrent the extra overhead.

Perhapse yet an other solution is posible ?

We found this response to be very helpful but we are having trouble implementing it. It seems to work great if the schema owner makes the change that cause the trigger to fire. However, when another user (who has privilege to change the master table) makes a change, the job gets queued up but fails when picked up for execution. We think this is because the LOG_USER is not the schema owner.

Any ideas?

make that trigger be:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger X
  2  after insert or update or delete on T
  3  declare
  4     l_job number;
  5  begin
  6     dbms_job.submit( l_job, 'dbms_mview.refresh( ''OPS$TKYTE.T_MV'' );' );
  7  end;
  8  /

Trigger created.

that is, put the SCHEMA NAME in the refresh call (else dbms_mview puts USER in there and it'll try to refresh USER.T_MV and that mv doesn't exist.

sorry about that! 

dbms_job.submit( lv_checkjob_num, 'DEAL_REFRESH_PACKAGE.deal_mv_refresh;' );

I have submitted JOB command to refresh the materailized view but nothing happens why?i am able to see the record inuser_jobs

I checked the JOB_que_processes = 1 and job_QUE_INTERVAL = 60 sec.
After changing JOB_QUE_PROCESSES we have shutdown and restart?we are running oracle 8.1.7.4

Hi Tom,

1. Let us assume that job_queue_interval=60 seconds and the number of jobs are too huge to finish within 60 seconds. Next Job execution should start even if the current queue is not ye over. What will happen in this case?

2. What will be the impact on DB performance if so many complex jobs are scheduled?

When i try to refresh the materialized view in a group(using dbms_refresh.refresh('Group Name') it is supposed to do delete then Insert.If above statement is correct why i am getting zero record in the Middle of refresh when i try to do count(*) from different session......

It's doing delete & Insert ,i found it writing a trigger on a MV table for delete.The command i used to create a group is.
Begin
dbms_refresh.make(
name =>'deal_dtl_refresh',
list =>'deal_dtl_mv',
next_date =>sysdate,
interval =>'sysdate+1/24',
implicit_destroy =>true);
end;

>>turn on sql_trace, run the dbms_refresh and look at the >>sql
Tom You are right it's doing Truncate & Insert Instead of delete and Insert.What i am doing wrong here,is my creating MV group is wrong.
This is how i created the MV group
Begin
dbms_refresh.make(
name =>'deal_dtl_refresh',
list =>'deal_dtl_mv',
next_date =>sysdate,
interval =>'sysdate+1/24',
implicit_destroy =>true);
end;

Hello Tom.

Hope all is well.
Similar to the delete/truncate question:

We have a large, complex MV (~280M rows).
We are running v11.2.0.2, and the MV script goes something like this:

create materialized view big_kahuna_mv
tablespace large_tabspace
nologging noparallel compress
build immediate refresh force
start with sysdate
next trunc(sysdate+1)+(2/24)
with primary key
as
select . . .
15 columns from 6 tables (sizes vary from 5M-400M rows)
using a WITH, UNION, etc. all sorts of conditions

In the table which was created, we had bitmap indexes on 5 of the columns.
The initial build and post-build index creation went pretty well (about 20 minutes).

But when the refresh time rolled around at 2:00am, we found that the refresh was doing a DELETE.
We were hoping for it to do a TRUNCATE + INSERT-APPEND.
After many hours of trying to delete all those rows and bitmaps, the SID was eventually euthanized.

There is no query rewrite considerations here.
The table access is via one or more of the bitmapped columns.
Our user's queries change on an ad hoc basis.
The table contents are to be refreshed on a daily basis.

Any thoughts as to how we might adjust the definition and/or management of the big MV?

Thanks and Best Regards,
- J

This solution figuratively saved my life. I also had a complex materialized view which was ON DEMAND. This solution with the addition of the schema name before the materialized view saved my life. Tom I have a scenario which I would like to share with you.

Lets imagine there are 500 users which open up a page at the same time (some sort of timed form submission). On this page there are three views which are being used. All three views are complex, am I better off with a materialized view or the simple views. Is using the materialized view concept here valid, if not what are the alternatives.