This content has been marked as final. Show 5 replies
create table trigger_action_table (action varchar2)
insert into trigger_action_table
Put a trigger on the table.
after update, insert, delete
then update trigger_action_table
set action = 'Y'
dba_Scheduled job, run every minute
select action from trigger_action_table
if action = Y
fast refresh your mview
set action = 'N'
Note: If your mview takes longer then 1 minute to refresh, youre in trouble. But thats separate as to "how would I do it"
Note: If your mview takes longer then 1 minute to refresh, youre in trouble. But thats separate as to "how would I do it"Should I make it 1 hr?
But it seems the process is becoming complicated :(
What is the default refresh time of materialized view again? I understand that if I set the refresh time every 1AM, it will run all the time, even if there is no data to be updated right?
Also I think the materialized view automatically creates its own scheduled job?
Edited by: KinsaKaUy? on 29-May-2012 02:38
if you set your view to refresh at 1am, then it will refresh at 1am. the default is what you set it to.
But from your first post, you want to build logic into it you cant do this without having the trigger.
Actually, just had a really big D'OH
If youve built it fast refresh any change is automatically captured in the snapshot log, when you fast refresh, the refresh automatically refreshes any changed row. you dont need to check for changes, the log does that for you.
But the same problem remains, if the updates take longer then 1 minute, then your in trouble. Now, that said, it shouldnt really unless youre talking HUGE updates, but you just dont want to be there. GEt your minimum window you want to live with and schedule for that. Couple of test timings and you should be fine.