lunes, 1 de agosto de 2016

The free feature for Oracle to create materialized views in all release, how to update a materialized view from a trigger

It's called a package

First we have the select, of one or more tables; and the table to store it
Second we create a procedure that insert the query in a table
Third we update it
 1) if we want a transactional update, we create a procedure that inserts the values for the new records in the trigger of the tables
 2) if we want a log fast refresh, we create a log that stores id of records we want to be updated
 3) if we want a full update, we create job
 For the update process depending the option we have to create a procedure, add to the trigger/job and that all.
 Aditionally we have a validation procedure that periodically if he found differences can generate a full or partial refresh.

Create a package
  insert into table
  select from tableA,tableB
  -- note in this refresh you can put
  This must be in a trigger update and or delete
   insert into table
   select from tableA,tableB
    where column=:new.value and column2=:new.value2

   insert into table
   select from tableA,tableB
     where not exists in table

 Procedure:PartialRefreshperFunction using log
  This requires a table that stores ids of modified rows, and a trigger that inserts the rowid when the needed columns of the table are modified, and generates a refresh only of those records in the database.
   insert into table
   select from tableA,tableB
     where not exists in table
 Procedure:fast refresh fast
  delete from;

 Procedure:fast refresh online
  truncate table;

    a select from tableA,tableB
    b select fro mtable
  select count(*) from   
  (select from a
  select from b
  union all
  select from b
  select from a)
  if count>0 then
   fullrefresh; -fast or online it dependes on you
  end if;
 create job executes validation periodically, depending the tables, etc.
 create job if using log to update the materialized view periodically
 optionally, create jobs makes partial insert, to reinforce validation.
 optionally, makes a full refresh


No hay comentarios.:

Publicar un comentario