7 Replies Latest reply: Sep 13, 2013 2:18 PM by 889367 RSS

    before insert trigger to populate a sequence

    889367

      10.2.0.2 & 11.2.0.2 both on RHEL 5.5

       

      I know how to use a trigger to assign a sequence value for a table on insert and I've seen Tom Kyte's comments on not doing that because of performance implications for bulk loading and scalability. But I'm trying to decide when and if I should use this. I wouldn't consider it but we've got Informatica developers that insist on writing dynamic sql functions to pull values for sequences to use in inserts because they can't reference the nextval in the their workflows. My thought is that doing a trigger/sequence combo is a lesser of two evils and we should do that where the tables in question are not going to be undergoing large bulk loads.

       

       

      Also, i've seen that with 11g you can directly assign a nextval to :new.col, but i've yet to see what the reasoning on doing so is. Does this method perform better or is it just less coding?

       

      Here's a sample test i've ran to see how differently they perform, and it's not a huge difference between to two.

       

      SQL> DROP FUNCTION DBABEN.TEST_SEQ_FUNC;
      DROP FUNCTION DBABEN.TEST_SEQ_FUNC
      *
      ERROR at line 1:
      ORA-04043: object TEST_SEQ_FUNC does not exist 
      
      
      Elapsed: 00:00:00.02
      SQL> DROP SEQUENCE DBABEN.SEQ_A;
      DROP SEQUENCE DBABEN.SEQ_A
                           *
      ERROR at line 1:
      ORA-02289: sequence does not exist 
      
      
      Elapsed: 00:00:00.01
      SQL> DROP SEQUENCE DBABEN.SEQ_B;
      DROP SEQUENCE DBABEN.SEQ_B
                           *
      ERROR at line 1:
      ORA-02289: sequence does not exist 
      
      
      Elapsed: 00:00:00.01
      SQL> DROP TABLE DBABEN.TEST1;
      DROP TABLE DBABEN.TEST1
                        *
      ERROR at line 1:
      ORA-00942: table or view does not exist 
      
      
      Elapsed: 00:00:00.14
      SQL> DROP TABLE DBABEN.TEST2;
      DROP TABLE DBABEN.TEST2
                        *
      ERROR at line 1:
      ORA-00942: table or view does not exist 
      
      
      Elapsed: 00:00:00.02
      SQL> DROP TRIGGER DBABEN.TEST1_SEQ_BI;
      DROP TRIGGER DBABEN.TEST1_SEQ_BI
      *
      ERROR at line 1:
      ORA-04080: trigger 'TEST1_SEQ_BI' does not exist 
      
      
      Elapsed: 00:00:00.01
      SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
      SQL> 
      SQL> CREATE OR REPLACE FUNCTION DBABEN.TEST_SEQ_FUNC (IN_SEQ_NAME IN VARCHAR2)
        2  RETURN NUMBER
        3  IS
        4  RET_VAL NUMBER;
        5  BEGIN
        6  EXECUTE IMMEDIATE 'select DBABEN.'||IN_SEQ_NAME||'.nextval from dual' INTO RET_VAL;
        7  RETURN RET_VAL;
        8  END;
        9  /
      
      Function created.
      
      Elapsed: 00:00:00.02
      SQL> 
      SQL> CREATE SEQUENCE DBABEN.SEQ_A
        2       START WITH 1
        3       INCREMENT BY 1
        4       NOMINVALUE
        5       MAXVALUE 9999999999999999999999999999
        6       NOCYCLE
        7       NOCACHE
        8       NOORDER
        9  ;
      
      Sequence created.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> CREATE SEQUENCE DBABEN.SEQ_B
        2       START WITH 1
        3       INCREMENT BY 1
        4       NOMINVALUE
        5       MAXVALUE 9999999999999999999999999999
        6       NOCYCLE
        7       NOCACHE
        8       NOORDER
        9  ;
      
      Sequence created.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> CREATE TABLE DBABEN.TEST1 (COL1 NUMBER, COL2 VARCHAR2(50));
      
      Table created.
      
      Elapsed: 00:00:00.01
      SQL> CREATE UNIQUE INDEX DBABEN.TEST1_PK ON DBABEN.TEST1(COL1);
      
      Index created.
      
      Elapsed: 00:00:00.01
      SQL> ALTER TABLE DBABEN.TEST1 ADD CONSTRAINT PK_TEST1 PRIMARY KEY (COL1) USING INDEX DBABEN.TEST1_PK;
      
      Table altered.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> CREATE TABLE DBABEN.TEST2 (COL1 NUMBER, COL2 VARCHAR2(50));
      
      Table created.
      
      Elapsed: 00:00:00.00
      SQL> CREATE UNIQUE INDEX DBABEN.TEST2_PK ON DBABEN.TEST2(COL1);
      
      Index created.
      
      Elapsed: 00:00:00.01
      SQL> ALTER TABLE DBABEN.TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (COL1) USING INDEX DBABEN.TEST2_PK;
      
      Table altered.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> CREATE TRIGGER DBABEN.TEST1_SEQ_BI BEFORE INSERT ON DBABEN.TEST1 FOR EACH ROW
        2  BEGIN
        3   SELECT DBABEN.SEQ_A.NEXTVAL INTO :NEW.COL1 FROM DUAL;
        4  END;
        5  /
      
      Trigger created.
      
      Elapsed: 00:00:00.01
      SQL> 
      SQL> set serveroutput on
      SQL> execute runstats_pkg.rs_start;
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:00:00.20
      SQL> declare
        2   val1 varchar2(50) := 'A';
        3  begin
        4    for i in 1..1000000 loop
        5           insert into dbaben.test1 ( col2 ) values ( val1 );
        6    end loop;
        7    commit;
        8  end;
        9  /
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:02:40.92
      SQL> execute runstats_pkg.rs_middle;
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:00:00.12
      SQL> declare
        2   val1 varchar2(50) := 'A';
        3   val2 number;
        4  begin
        5    for i in 1..1000000 loop
        6       select DBABEN.TEST_SEQ_FUNC('SEQ_B') into val2 from dual;
        7           insert into dbaben.test2 values (val2, val1);
        8    end loop;
        9    commit;
       10  end;
       11  /
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:03:04.00
      SQL> execute runstats_pkg.rs_stop(p_difference_threshold=>9);
      Run1 ran in 16099 hsecs                                                         
      Run2 ran in 18399 hsecs                                                         
      run 1 ran in 87.5% of the time                                                  
                                                                                         
      Name                                            Run1        Run2        Diff    
      LATCH.session timer                               53          63          10    
      LATCH.library cache load lock                     20          30          10    
      LATCH.transaction branch allocation              116         126          10    
      LATCH.channel anchor                              64          74          10    
      STAT...cluster key scan block gets               420         430          10    
      STAT...immediate (CURRENT) block cleanou       3,779       3,769         -10    
      LATCH.object stats modification                   19           8         -11    
      STAT...workarea memory allocated                 -29         -17          12    
      STAT...write clones created in foregroun           5          17          12    
      STAT...sorts (rows)                           38,266      38,253         -13    
      LATCH.ASM db client latch                        162         147         -15    
      LATCH.dictionary lookup                           23           8         -15    
      STAT...parse time elapsed                        318         300         -18    
      STAT...dirty buffers inspected                     0          23          23    
      STAT...parse time cpu                            369         340         -29    
      STAT...buffer is pinned count                    113          81         -32    
      LATCH.message pool operations parent lat          57          24         -33    
      STAT...pinned buffers inspected                   39           4         -35    
      LATCH.object queue header heap                    93          56         -37    
      LATCH.shared pool simulator                      199         149         -50    
      LATCH.resmgr group change latch                   52         110          58    
      STAT...index crx upgrade (positioned)            187         249          62    
      STAT...shared hash latch upgrades - no w         191         253          62    
      STAT...session cursor cache hits           1,000,322   1,000,384          62    
      LATCH.post/wait queue                            347         410          63    
      LATCH.compile environment latch                   44         109          65    
      LATCH.event group latch                           44         109          65    
      LATCH.resmgr:actses change group                  43         109          66    
      LATCH.global KZLD latch for mem in SGA            43         109          66    
      LATCH.FOB s.o list latch                         182         115         -67    
      STAT...index scans kdiixs1                       334         407          73    
      LATCH.resmgr:resource group CPU method            38         112          74    
      LATCH.PL/SQL warning settings                     61         136          75    
      STAT...opened cursors cumulative           1,000,472   1,000,548          76    
      STAT...parse count (total)                 1,000,549   1,000,628          79    
      LATCH.dml lock allocation                  2,001,420   2,001,339         -81    
      STAT...bytes received via SQL*Net from c       1,448       1,545          97    
      STAT...db block gets from cache            6,707,836   6,707,732        -104    
      STAT...db block gets                       6,707,836   6,707,732        -104    
      STAT...table fetch by rowid                      305         410         105    
      LATCH.channel operations parent latch          1,260       1,374         114    
      LATCH.threshold alerts latch                     126           4        -122    
      STAT...enqueue requests                    2,003,345   2,003,475         130    
      STAT...enqueue releases                    2,003,345   2,003,475         130    
      LATCH.dummy allocation                            83         221         138    
      LATCH.process group creation                      83         221         138    
      LATCH.OS process: request allocation              83         221         138    
      LATCH.process allocation                          83         221         138    
      LATCH.parameter table management                  85         223         138    
      LATCH.resmgr:free threads list                    81         221         140    
      LATCH.channel handle pool latch                   91         231         140    
      LATCH.resmgr:active threads                       87         227         140    
      LATCH.OS process allocation                      141         282         141    
      LATCH.Consistent RBA                           1,871       1,726        -145    
      LATCH.mostly latch-free SCN                    1,897       1,747        -150    
      LATCH.lgwr LWN SCN                             1,874       1,724        -150    
      LATCH.JS queue state obj latch                   960       1,110         150    
      LATCH.session state list latch                   165         345         180    
      STAT...redo entries                        4,025,720   4,025,901         181    
      LATCH.sequence cache                       2,000,144   2,000,345         201    
      LATCH.OS process                                 123         336         213    
      STAT...db block changes                    8,064,475   8,064,703         228    
      LATCH.file cache latch                           617         375        -242    
      STAT...no work - consistent read gets          1,015       1,284         269    
      LATCH.user lock                                  162         442         280    
      LATCH.library cache lock allocation              218         503         285    
      LATCH.In memory undo latch                     1,036         688        -348    
      LATCH.session switching                          197         563         366    
      LATCH.client/application info                    286         659         373    
      STAT...buffer is not pinned count              1,153       1,527         374    
      LATCH.active service list                        503         880         377    
      LATCH.library cache pin                    4,001,694   4,002,121         427    
      STAT...calls to kcmgas                     1,053,347   1,052,745        -602    
      STAT...redo ordering marks                    51,228      50,614        -614    
      STAT...redo subscn max counts                 54,129      53,475        -654    
      LATCH.SQL memory manager workarea list l       3,809       4,567         758    
      LATCH.list of block allocation                 2,486       1,604        -882    
      LATCH.redo allocation                          7,613       6,603      -1,010    
      LATCH.active checkpoint queue latch            1,565         552      -1,013    
      LATCH.enqueues                             1,008,739   1,007,721      -1,018    
      LATCH.SGA IO buffer pool latch                   320       1,436       1,116    
      LATCH.session allocation                   4,001,537   4,002,826       1,289    
      STAT...consistent gets - examination       2,004,160   2,006,054       1,894    
      LATCH.redo writing                             8,139       6,206      -1,933    
      STAT...recursive cpu usage                    15,481      17,417       1,936    
      STAT...CPU used by this session               15,782      17,921       2,139    
      STAT...CPU used when call started             15,782      17,921       2,139    
      STAT...session logical reads               8,715,273   8,717,455       2,182    
      STAT...consistent gets from cache          2,007,437   2,009,723       2,286    
      STAT...consistent gets                     2,007,437   2,009,723       2,286    
      STAT...Elapsed Time                           16,110      18,412       2,302    
      STAT...DB time                                16,097      18,400       2,303    
      LATCH.enqueue hash chains                  4,017,742   4,015,275      -2,467    
      LATCH.messages                                11,280       8,207      -3,073    
      LATCH.shared pool                              4,142       7,535       3,393    
      STAT...IMU Redo allocation size               34,156      37,552       3,396    
      STAT...hot buffers moved to head of LRU       14,983      20,026       5,043    
      LATCH.row cache objects                    7,015,405   7,021,192       5,787    
      LATCH.undo global data                     3,035,853   3,026,139      -9,714    
      LATCH.cache buffer handles                    22,068      11,810     -10,258    
      STAT...free buffer requested                  50,816      33,487     -17,329    
      LATCH.transaction allocation                  81,547     102,013      20,466    
      STAT...undo change vector size           384,659,248 384,680,304      21,056    
      LATCH.library cache                        7,007,411   7,030,401      22,990    
      STAT...free buffer inspected                   5,828      29,489      23,661    
      STAT...IMU undo allocation size              101,208      68,064     -33,144    
      LATCH.checkpoint queue latch                  38,976      83,349      44,373    
      LATCH.object queue header operation          220,296     266,592      46,296    
      LATCH.simulator lru latch                     25,498      75,501      50,003    
      LATCH.cache buffers lru chain                 52,658     151,989      99,331    
      STAT...session uga memory                    130,976           0    -130,976    
      STAT...redo size                        ########################     142,776    
      LATCH.session idle bit                     1,205,082   1,047,276    -157,806    
      STAT...session uga memory max                314,392     130,976    -183,416    
      STAT...session pga memory max                262,144           0    -262,144    
      STAT...session pga memory                   -262,144           0     262,144    
      STAT...execute count                       3,000,546   4,000,623   1,000,077    
      STAT...calls to get snapshot scn: kcmgss   3,002,663   4,002,815   1,000,152    
      STAT...recursive calls                    16,005,433  17,006,257   1,000,824    
      LATCH.simulator hash latch                 2,304,730     664,790  -1,639,940    
      LATCH.library cache lock                   8,195,779  10,021,069   1,825,290    
      LATCH.cache buffers chains                36,152,269  40,842,410   4,690,141    
                                                                                         
      Run1 latches total versus runs -- difference and pct                            
      Run1        Run2        Diff       Pct                                          
      82,442,582  87,428,038   4,985,456     94.30%                                   
      
      PL/SQL procedure successfully completed.
      
      Elapsed: 00:00:00.28
      SQL> exit
      

       

       

       

      Thanks

        • 1. Re: before insert trigger to populate a sequence
          SomeoneElse

          > Here's a sample test i've ran to see how differently they perform, and it's not a huge difference between to two.


          I don't see where you've tested that.  You have one method that uses dynamic sql and one that uses this:


          SELECT DBABEN.SEQ_A.NEXTVAL INTO :NEW.COL1 FROM DUAL; 


          11.1 and higher now allows you to do this:


          :NEW.COL1 := DBABEN.SEQ_A.NEXTVAL;


          No select from dual needed, thereby eliminating a sql statement that must be parsed, etc.


          • 2. Re: before insert trigger to populate a sequence
            Frank Kulash

            Hi,

             

            I think a trigger is the best solution here, but I'm not sure I understand the issue with the Informativa developers.

            You could have a trigger and a function.  In the trigger, say

             

            IF  :NEW.col1  IS NULL

            THEN

                SELECT  dbaben.seq_a.NEXTVAL

                INTO    :NEW.col1

                FROM    dual; 

            END IF;

             

            Of course, you'll have problems if somebody inserts a col1 value that did not come from the sequence or the function.

            Think carefully before using this option; it could give you the worst of both worlds.

             

            Not having to use dual just makes the code a little cleaner.

             

            Too bad you're not using Oracle 12; it has a built-in feature for this.

            • 3. Re: before insert trigger to populate a sequence
              Hoek

              In addition to SE  +edit+ and Frank, who posted while I was still typing on my ancient, slow laptop:

              Read up on context switching between the SQL and PL/SQL engines.

              I don't call a trigger a trigger anymore, I call them 'deliberatly programmed and compiled context switching objects'.

              Oracle 12c now allows:

              http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#NEWFT156

              and

              http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#NEWFT158

               

              Message was edited by: Hoek

              • 4. Re: before insert trigger to populate a sequence
                marcusafs

                But you have to admit it is easier to type TRIGGER.

                • 5. Re: before insert trigger to populate a sequence
                  889367

                  What's the benefit of including "IF  :NEW.col1  IS NULL" statement? If I leave it out and someone tries to insert a value not using the trigger it changes the value for me. I can see that being good and bad, but it keeps them from not using the sequence.

                  • 6. Re: before insert trigger to populate a sequence
                    rp0428

                    889367 wrote:

                     

                    What's the benefit of including "IF  :NEW.col1  IS NULL" statement? If I leave it out and someone tries to insert a value not using the trigger it changes the value for me. I can see that being good and bad, but it keeps them from not using the sequence.

                    The benefit is that it allows you to manually assign the column value if you want to. Whether that is a 'benefit' for your use case or not is for you to decide.

                    But I'm trying to decide when and if I should use this. I wouldn't consider it but we've got Informatica developers that insist on writing dynamic sql functions to pull values for sequences to use in inserts because they can't reference the nextval in the their workflows.

                    Don't use Informatica to do something that can be done using Oracle. The strength and utility of an ETL tool is in doing things that the database either can NOT do or cannot do efficiently: for example pulling data from multiple databases and flat files for insert into a target database. The goal being to get ALL of the data into the target database as quickly and efficiently as possible. Then you can apply the full power of the target database to ALL of the data.

                    • 7. Re: before insert trigger to populate a sequence
                      889367

                      Totally agree about not doing what can be by the database in Infa, it's hard to regulate that in our environment though.