Re: MView refresh question

From: Sriram Kumar <k.sriramkumar_at_gmail.com>
Date: Tue, 29 May 2012 19:33:59 +0530
Message-ID: <CAEq4C0fCEZsEQbBUD=UjyBLTScnrc1ENFO9aJmFsjXCa_dL3RA_at_mail.gmail.com>

Hi,
I scripted this as an example

• this is the base table

create table tb_source as select * from dba_objects where 1=0;

• pk on the table

alter table tb_source add primary key (object_id);

--mview log for fast refresh

create materialized view log on tb_source;

--mview

CREATE MATERIALIZED VIEW mv_simple
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT * FROM tb_source;

--this is my alert table for simplicity. this would be designed as AQ table with a call back on it

create table tb_alert (name varchar2(255), event_date date);

• this is an trigger on mview. has been created as an statement level trigger
• could be created as a row level trigger if there is a need

create or replace trigger my_alert_trigger after insert on mv_simple

begin

insert into tb_alert values (' Record Inserted',sysdate);

end;

• sample insert

 insert into tb_source select * from dba_objects where object_id is not null and rownum<10000;

 commit;

• now on commit the mview would be refreshed and on the refresh of the mview, the tb_alert would have records.
• this trigger could do a AQ and there would be a pl/sql routine as a call back that would wake up on the message to do rest of processing

hope this helps

best regards

sriram kumar

On Tue, May 29, 2012 at 5:03 PM, rjamya <rjamya_at_gmail.com> wrote:

> Thanks Sriram, well the idea is to _detect_ if there has been any change
> (especially inserts), the process will figure out exactly which rows based
> on status. sometimes we have tens of rows changed in a minute, sometimes it
> has been a hundred k, so all we need to detect is " were there any inserts
> done in the last mv refresh ", if so, we will trigger sleeping process, if
> not we will let it sleep some more.
>
> Hopefully this week we will get to rest some ideas ... oh and you are not
> late, we are still in the eval phase
>
> Thanks
> Raj
>
>
> On Sat, May 26, 2012 at 4:48 AM, Sriram Kumar <k.sriramkumar_at_gmail.com>wrote:
>
>> Hi,
>>
>> apologies for jumping in late. How about a row-level trigger on the mview
>> that performs a enqueue of the PK on AQ ? and then the poller can do a look
>> up based on the PK?
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 29 2012 - 09:03:59 CDT