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