This discussion is archived
7 Replies Latest reply: Sep 13, 2013 12:18 PM by 889367 RSS

before insert trigger to populate a sequence

889367 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

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

  • 5. Re: before insert trigger to populate a sequence
    889367 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points