5 Replies Latest reply: May 29, 2012 4:55 AM by 846231 RSS

    materialized view

    846231
      Hi All,

      Is there an option for a materialized view to be updated 1 minute later?

      I mean I have a table EMP and a materialized view of this table as EMP_MV.

      My requirement is that, if I insert, or update, or delete a row in EMP, after a minute it will be replicated in EMP_MV.
      If nothing change in a table for a day then no action is being done also for a day.


      Thanks a lot,
        • 1. Re: materialized view
          713555
          create table trigger_action_table (action varchar2)

          insert into trigger_action_table
          values ('N')'

          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
          update trigger_action_table
          set action = 'N'
          end if.

          Note: If your mview takes longer then 1 minute to refresh, youre in trouble. But thats separate as to "how would I do it"
          • 2. Re: materialized view
            846231
            Thanks dee,
            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?


            Thanks,

            Also I think the materialized view automatically creates its own scheduled job?

            Edited by: KinsaKaUy? on 29-May-2012 02:38
            • 3. Re: materialized view
              713555
              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.
              • 4. Re: materialized view
                viconstg
                Refresh time of mat. view depends on its definition. You can refer to the docs: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6002.htm#SQLRF01302
                In particular, note the option NEXT here http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_6002.htm#i2064161
                • 5. Re: materialized view
                  846231
                  I thank you all,