FORALL and STATEMENT level trigger

Just an addendum on one of the points in my previous post.

In that post, I pointed out, amongst other things, that for a FORALL .. INSERT statement, the statement level trigger fired only once (basic evidence in previous post).

This was something which surprised me.

Just from further observations:

  • FORALL INSERT – Single statement – i.e. Statement trigger fires once.
  • FORALL UPDATE – Multiple statements – i.e. Statement trigger fires multiple times.
  • FORALL DELETE – Multiple statements – i.e. Statement trigger fires multiple times.

Which kind of backs up my original assertion in the forum thread, which was that when working with a collection and using statement level trigger work, a single INSERT/UPDATE/DELETE using TABLE (CAST ( )) should be preferable to FORALL.

The constant requirement to re-review the basics, previously known “facts”, knowledge gaps and also your assumptions is one of the things which makes working with Oracle so interesting.

SQL> SET serveroutput ON  
SQL> 
SQL> CREATE TABLE test_bulk  
  2  (test_id    NUMBER  
  3  , id_test_1  NUMBER  
  4  , id_test_2  NUMBER  
  5  ); 

Table created.

SQL> CREATE OR REPLACE TRIGGER test_asi  
  2  AFTER INSERT OR UPDATE OR DELETE
  3  ON TEST_BULK  
  4  REFERENCING NEW AS New OLD AS Old  
  5  DECLARE  
  6  begin  
  7     DBMS_OUTPUT.PUT_LINE('expensive action');  
  8  end;  
  9  /

Trigger created.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       INSERT INTO test_bulk(id_test_1,id_test_2) VALUES (1,my_collection_test(i));  
 11  END;  
 12  /  
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk; 

  COUNT(*)
----------
        14

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4  BEGIN  
  5     SELECT LEVEL BULK COLLECT  
  6     INTO   my_collection_test  
  7     FROM   dual  
  8     CONNECT BY LEVEL < 15;  
  9     FORALL i IN 1 .. my_collection_test.COUNT  
 10       UPDATE test_bulk
 11       SET    id_test_1 = 1
 12       ,      id_test_2 = my_collection_test(i)
 13      WHERE id_test_2 = my_collection_test(i);  
 14  END;  
 14  / 
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> DECLARE  
  2    TYPE my_collection IS TABLE OF NUMBER;  
  3    my_collection_test  my_collection;  
  4    my_collection_test2  my_collection;  
  5  BEGIN  
  6     SELECT LEVEL BULK COLLECT  
  7     INTO   my_collection_test  
  8     FROM   dual  
  9     CONNECT BY LEVEL < 15;  
 10     FORALL i IN 1 .. my_collection_test.COUNT  
 11       DELETE test_bulk
 12       WHERE  id_test_2 = my_collection_test(i)
 13       RETURNING id_test_2 BULK COLLECT INTO my_collection_test2;  
 14  END;  
 15  /  
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action
expensive action

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM test_bulk;

  COUNT(*)
----------
         0

SQL> 
About these ads

12 Responses to FORALL and STATEMENT level trigger

  1. Rob van Wijk says:

    Hello Dominic,

    Surprising indeed. Thanks for the interesting post.
    On which version(s) did you test this?

    Regards,
    Rob.

    • Dom Brooks says:

      Hi Rob,

      Have seen same results in 9.2.0.8 and 11.2.0.2.

      I don’t think I’m doing anything daft?

      Friday I thought I was an idiot for not knowing this (based on FORALL + INSERT observation), today I feel better as there seems to be a difference between INSERT and (UPDATE or DELETE).

      Cheers,
      Dominic

  2. Gary says:

    INSERT…VALUES may even work differently from INSERT…SELECT.

    The INSERT…VALUES doesn’t really depend on its sequence or table state. In theory they can be run in any order (ignoring any triggers or exceptions from unique constraints, and assuming absolute values not scalar subqueries) and achieve the same thing. They could practically be re-written as a single statement of the form INSERT…SELECT .. FROM TABLE(collection_variable)
    11gR2 even allow for direct path inserts with FORALL

    http://www.oracle-base.com/articles/11g/AppendValuesHint_11gR2.php

    UPDATEs and DELETEs are different. The order in which they are executed might matter (eg update 1 adds 5 to a column value and update 2 doubles the value).

    • Alex Nedoboi says:

      Direct path inserts with FORALL make perfect sense. I’m more fan of append_values.

      create table t1 (id number(10));
      
      begin
         for i in 1..100 loop
            insert /* append_values */ into t1 values (i);
            commit;
         end loop;
      end;
      /
      
      select blocks, num_rows from user_tables where table_name = 'T1';
      
          BLOCKS   NUM_ROWS
      ---------- ----------
             100        100
      
      
    • Dom Brooks says:

      Thanks Gary – I get what you’re saying.

      It’s not a big deal but it is surprising (to me).

      And if you’re trying to make code more efficient and you happen to have some triggers in the picture you can’t get rid of, then it might be significant.

  3. Dom Brooks says:

    Had quite a few hits on this article today. I’m guessin it’s because of searches related to the subject of the question on plsql challenge today:

    http://www.plsqlchallenge.com/pls/apex/f?p=10000:651:5353740396373687::NO::P651_QUIZ_ID:2104

    • Gary says:

      Now there’s a thought for building an audience. “The answer to next Tuesday’s question is hidden somewhere on my blog….”

      • Dom Brooks says:

        Yes, indeed, good point – find an obscure aspect about which there aren’t a lot of articles out there. Blog about it. Then do a question. Bingo.

        Like an online technical treasure hunt.

        An online treasure hunt…. do the words Kit Williams and Masquerade mean anything to you?

  4. _Nikotin says:

    It looks like a bug. On metalink I could find only “Bug 8463344: PSRC: COMPOUND TRIGGER DOES NOT FIRE AS EXPECTED WHEN INSTEAD TRIGGER EXISTS” that is related to the topic.

    • _Nikotin says:

      i.e. “Bug 7641320: COMPOUND TRIGGER IS NOT TRIGGERED FROM TRIGGERING STATEMENTS IN A LOOP”

      • Dom Brooks says:

        _Nikotin,

        Not a bug – just undocumented.

        I came across it first in 9i when optimising code with expensive actions on row triggers. I moved the action to an after statement trigger with the for row triggers populating collections (with the ability to flush to the expensive action when the collection hit a certain size). And I explained to another developer that for the trigger stuff to have the best effect, it had to be an INSERT…SELECT becauses a FORALL INSERT would trigger the after statement for each row. I was wrong.

        Thanks for stopping by.

        Cheers,
        Dominic

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

Follow

Get every new post delivered to your Inbox.

Join 68 other followers

%d bloggers like this: