Let’s assume you have to track changes in stage table (in this case tstage table) and insert all inserted rows in trigger_test table.

Once of your logical choices is to create a trigger (there are a couple of other options related to AQ/streams).

Before 11g version, you can create before/after row trigger (where later is slightly more efficient as Oracle will read data blocks – logical reads, only once for both the triggering statement and the trigger ).

The other option is to create a package, package variables and a several procedures to track changes
more efficiently (by using bulk statements).

From 11g version, there is much more natural/faster and less error prone way of writing the same code: by using the compound triggers.

In the following examples I’ll show how fast is to track changes when inserting 1 million rows by using classical (before row level trigger) and compare performance with compound trigger.



--regular trigger example
create or replace trigger trigger_test_normal
before insert on tstage
for each row 

begin 
insert /*+ append */ into trigger_test (
item, 
dept,
class,
subclass,
pack_ind,
loc_type,
location,
tran_date,
post_date,
tran_code,
adj_code,
units,
total_cost,
total_retail,
ref_no_1,
ref_no_2,
gl_ref_no,
old_unit_retail,
new_unit_retail,
pgm_name,
sales_type,
vat_rate,
av_cost,
ref_pack_no,
iftd_rowid,
tran_data_timestamp
)
values
(
:new.item,
:new.dept,
:new.class,
:new.subclass,
:new.pack_ind,
:new.loc_type,
:new.location,
:new.tran_date,
:new.post_date,
:new.tran_code,
:new.adj_code,
:new.units,
:new.total_cost,
:new.total_retail,
:new.ref_no_1,
:new.ref_no_2,
:new.gl_ref_no,
:new.old_unit_retail,
:new.new_unit_retail,
:new.pgm_name,
:new.sales_type,
:new.vat_rate,
:new.av_cost,
:new.ref_pack_no,
:new.iftd_rowid,
:new.tran_data_timestamp
);
end;

--inserting 1 million rows into tstage table
begin
  insert /*+ append */ into tstage
  select * from tsource where rownum <= 1000000;
  commit;
end;

--elapsed time 
47,5 sec 


--compound trigger example 

create or replace trigger trigger_test_compound 
for insert on tstage
compound trigger 
  type tdh_stage_ps_type is table of tstage%rowtype index by pls_integer;
  tdh_stage_ps_coll tdh_stage_ps_type;
  l_counter pls_integer := 0;

  before each row is 
  begin
    l_counter := l_counter + 1;    
    
    tdh_stage_ps_coll(l_counter).item := :new.item;
    tdh_stage_ps_coll(l_counter).dept := :new.dept;
    tdh_stage_ps_coll(l_counter).class := :new.class;
    tdh_stage_ps_coll(l_counter).subclass := :new.subclass;
    tdh_stage_ps_coll(l_counter).pack_ind := :new.pack_ind;
    tdh_stage_ps_coll(l_counter).loc_type := :new.loc_type;
    tdh_stage_ps_coll(l_counter).location := :new.location;
    tdh_stage_ps_coll(l_counter).tran_date := :new.tran_date;
    tdh_stage_ps_coll(l_counter).post_date := :new.post_date;
    tdh_stage_ps_coll(l_counter).tran_code := :new.tran_code;
    tdh_stage_ps_coll(l_counter).adj_code := :new.adj_code;
    tdh_stage_ps_coll(l_counter).units := :new.units;
    tdh_stage_ps_coll(l_counter).total_cost := :new.total_cost;
    tdh_stage_ps_coll(l_counter).total_retail := :new.total_retail;
    tdh_stage_ps_coll(l_counter).ref_no_1 := :new.ref_no_1;
    tdh_stage_ps_coll(l_counter).ref_no_2 := :new.ref_no_2;
    tdh_stage_ps_coll(l_counter).gl_ref_no := :new.gl_ref_no;
    tdh_stage_ps_coll(l_counter).old_unit_retail := :new.old_unit_retail;
    tdh_stage_ps_coll(l_counter).new_unit_retail := :new.new_unit_retail;
    tdh_stage_ps_coll(l_counter).pgm_name := :new.pgm_name;
    tdh_stage_ps_coll(l_counter).sales_type := :new.sales_type;
    tdh_stage_ps_coll(l_counter).vat_rate := :new.vat_rate;
    tdh_stage_ps_coll(l_counter).av_cost := :new.av_cost;
    tdh_stage_ps_coll(l_counter).ref_pack_no := :new.ref_pack_no;
    tdh_stage_ps_coll(l_counter).iftd_rowid := :new.iftd_rowid;
    tdh_stage_ps_coll(l_counter).tran_data_timestamp := :new.tran_data_timestamp;  
  end before each row;  

  after statement is 
    begin 
      forall counter in 1..tdh_stage_ps_coll.count()
        insert /*+ append */ into trigger_test 
        values tdh_stage_ps_coll(counter);
    end after statement;
end trigger_test_compound;  


begin
  insert /*+ append */ into tstage
  select * from tsource where rownum <= 1000000;
  commit;
end;

--elapsed time 
19,7 sec 


As you can see, compound trigger with bulk operations inside is very useful approach to track massive changes, and in this case much faster (19.7 sec against 47.5 sec with regular row level trigger).

I’ve seen such massive changes most often in ETL processes (when loading Data Warehouse) and with JMS/AQ massive messages exchange in integration of various systems.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.