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