Abstract Confusions

Complexity is not a cause of confusion. It is a result of it.

Compound Trigger – A Performance Improvement for Trigger Executions

In Oracle database 11g release version 1, among the enhancements in trigger execution an important and efficient feature known as ‘Compound Trigger’ is rolled out. This feature is particularly exciting because of the performance improvements it may bring in along with it.

Auditing Bulk INSERT/UPDATE of a Table Efficiently

Assume you have a transactional table, this table is highly accessed by a front end application which does a lot of inserts and updates every minute. You also have a functional requirement of keeping track of the changes against the values inserted / updated. The traditional setup one would have for such a scenario is to write a separate trigger each for AFTER INSERT and AFTER UPDATE to fetch the values and insert them into an auditing table as well.If there are frequent updates, inserts on this table, a trigger would worsen the performance and might increase the response time. Most of the time, database designers would advice to add an extra field bit, something like IS_UPDATED with value ‘Y’ or ‘N’. And schedule a database job to run every minute to scan the big table for UPDATED flag and insert them into audit table. This approach works really good and has no or very less performance overhead. Since you do all the work proceduraly, you control how they do and when. Above all, the execution is done later, thus not affecting the response time.

If you have the trigger to populate the auditing table, then performance might wary, and you might wonder, whether it is possible to improve the performance of trigger execution in case of bulk inserts or updates. Lets see how this is done with ‘Compound Triggers’.

Compound Triggers

Compound trigger is used to club one or more triggers and their timing into one single trigger. The compound trigger the following four timing points.

  1. BEFORE STATEMENT – Before INSERT/UPDATE/DELETE statement. Executed only once for a statement.
  2. BEFORE EACH ROW – Before the action can be applied for each row. Executed once for every row.
  3. AFTER EACH ROW – After the action is performed on each row. Executed once for every row.
  4. AFTER STATEMENT – After INSERT/UPDATE/DELETE statement. Executed only once.

Compound trigger can be used to audit bulk updates and do bulk insert into audit tables.

Now, enough said, we will try an example. Consider the simple table T and audit table for it T_AUD.

SQL> create table t
  2  (id      number primary key
  3  ,val     varchar2(10))
  4  /

Table created.

SQL> create table t_aud
  2  (id      number
  3  ,val     varchar2(10)
  4  ,times   timestamp
  5  ,muser   varchar2(30))
  6  /

Table created.

Now, lets create an UPDATE, INSERT trigger. I will set the timing point in AFTER EACH ROW and AFTER STATEMENT. If you have a need, try them on BEFORE STATEMENT and/or BEFORE EACH ROW. You may want to create only UPDATE trigger, in that case, just replace ‘FOR UPDATE OR INSERT’ with ‘FOR UPDATE’.

SQL> create or replace trigger track_t_trg
  2  for update or insert
  3  on t
  4  compound trigger
  5
  6  --- Common declarations...
  7  type t_aud_typ   is table of t_aud%rowtype
  8  index by pls_integer;
  9  --- Create a variable, now.
 10  v_t_aud_events   t_aud_typ;
 11  idx              pls_integer := 0;
 12
 13  ----------- Main Trigger Body ----------------
 14  --- Before statement
 15  before statement is
 16  begin
 17     ---  In BEFORE STATEMENT section.
 18     NULL;
 19  end before statement;
 20  --- Before each row st't
 21  before each row is
 22  begin
 23     --- In BEFORE EACH ROW STATEMENT section .
 24     NULL;
 25  end before each row;
 26  --- After each row section
 27  after each row is
 28  begin
 29     dbms_output.put_line('... In AFTER EACH ROW Section ');
 30     dbms_output.put_line('... Recording what is performed ');
 31     idx := idx + 1;
 32     v_t_aud_events(idx).id := :new.id;
 33     v_t_aud_events(idx).val := :new.val;
 34     v_t_aud_events(idx).times := systimestamp;
 35     v_t_aud_events(idx).muser := user;
 36  end after each row;
 37  --- After statement
 38  after statement is
 39  begin
 40     dbms_output.put_line('... In AFTER STATEMENT section ');
 41     dbms_output.put_line('... Inserting into T_AUD table ');
 42
 43     forall cnt in 1..v_t_aud_events.count()
 44            insert into t_aud
 45                  values v_t_aud_events(cnt);
 46
 47     dbms_output.put_line('... Inserted values successfully');
 48  end after statement;
 49  end track_t_trg;
 50  /

Trigger created.

SQL> sho err
No errors.

Now, lets try inserting few values into T. Note that since I have INSERT too defined in trigger, the values are inserted into T_AUD table as well.

SQL> select *
  2  from t
  3  /

no rows selected

SQL> select *
  2  from t_aud
  3  /

no rows selected

SQL> insert into t values (1, 'one')
  2  /
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER STATEMENT section
... Inserting into T_AUD table
... Inserted values successfully

1 row created.

SQL> insert into t values (2, 'two')
  2  /
----- Same DBMS OUTPUTS as above, omitting for brevity
1 row created.

SQL> insert into t values(5, 'five')
  2  /
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER STATEMENT section
... Inserting into T_AUD table
... Inserted values successfully

1 row created.

SQL> select *
  2  from t
  3  /

        ID VAL
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five

SQL> select *
  2  from t_aud
  3  /

        ID VAL        TIMES                        MUSER
---------- ---------- ---------------------------- --------
         1 one        18-MAY-10 12.23.59.939514 AM GURU
         2 two        18-MAY-10 12.24.14.349039 AM GURU
         3 three      18-MAY-10 12.24.25.698502 AM GURU
         4 four       18-MAY-10 12.24.37.835251 AM GURU
         5 five       18-MAY-10 12.24.48.711000 AM GURU

Game time now, lets do a bulk update.

SQL> update t
  2     set id = id + 1
  3  /
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER EACH ROW Section
... Recording what is performed
... In AFTER STATEMENT section
... Inserting into T_AUD table
... Inserted values successfully
5 rows updated.

SQL> select *
  2  from t_aud
  3  /

        ID VAL        TIMES                        MUSER
---------- ---------- ---------------------------- --------
         1 one        18-MAY-10 12.23.59.939514 AM GURU
         2 two        18-MAY-10 12.24.14.349039 AM GURU
         3 three      18-MAY-10 12.24.25.698502 AM GURU
         4 four       18-MAY-10 12.24.37.835251 AM GURU
         5 five       18-MAY-10 12.24.48.711000 AM GURU
         2 one        18-MAY-10 12.26.19.339795 AM GURU
         3 two        18-MAY-10 12.26.19.341057 AM GURU
         4 three      18-MAY-10 12.26.19.341364 AM GURU
         5 four       18-MAY-10 12.26.19.341626 AM GURU
         6 five       18-MAY-10 12.26.19.342089 AM GURU

Look at the DBMS outputs closely. You can notice that AFTER EACH ROW section is executed for each row the UPDATE statement affected. Even though this was five rows, the INSERT into T_AUD (in AFTER STATEMENT section ) happened only once. The affected row values are stored in row type array and once all rows are updated, they are bulk inserted into audit table in AFTER STATEMENT section.

By doing so, we have saved separate five insert calls which we would have done in with out Compound trigger. This is a good performance improvement. You can also set thresholds and flush the updates, if you think you are doing way too many updates. Another nice example from Oracle-Base on compound triggers here.

One response to “Compound Trigger – A Performance Improvement for Trigger Executions

  1. Pingback: Tweets that mention Compound Trigger – A Performance Improvement for Trigger Executions « Abstract Confusions -- Topsy.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: