Re: using sequences in triggers to generate identities

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 23 Apr 2007 15:34:52 -0700
Message-ID: <1177367691.728420@bubbleator.drizzle.com>

if wrote:
> Hi,
>
> ';m coming from a db2 background.
> I was wondering if in Oracle there is any performance (or other)
> difference between using a sequence in insert statements and using an
> insert trigger to do the same job.

CREATE TABLE t (
testcol NUMBER(10));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (testcol)
USING INDEX
PCTFREE 0; CREATE SEQUENCE seq_t;

set timing on

BEGIN
   FOR i IN 1 .. 100000 LOOP

     INSERT INTO t
     (testcol)
     VALUES
     (seq_t.NEXTVAL);

   END LOOP;
END;
/

set timing off

• 3 timings: 7.73 / 8.21 / 8.50

DROP SEQUENCE seq_t;

TRUNCATE TABLE t;

CREATE SEQUENCE seq_t;

CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t
FOR EACH ROW
BEGIN
   SELECT seq_t.NEXTVAL
   INTO :NEW.testcol
   FROM dual;
END;
/

set timing on

BEGIN
   FOR i IN 1 .. 100000 LOOP

     INSERT INTO t
     (testcol)
     VALUES
     (1);

   END LOOP;
END;
/

set timing off

• 3 timings: 11.53 / 11.67 / 11.68

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Apr 23 2007 - 17:34:52 CDT