slow performance with sequences

I'm curious to see if people are having the same slow performance with sequences that I'm having. In essence when I do a mass insert (525,000 records - it's a very narrow table) into a table without a sequence with a trigger the insert happens in two seconds; when I do the same with the trigger on and sequence used it takes 1 minute and 6 seconds. The table does not have any indexes. I cached the sequence up to 10000 and that made very little difference. This occurs in both 8i (8.1.7.4) and 9i. For what it's worth SQL Server does this in 6 seconds using an identity. (No I'm not trying to start a flame war - I just want to know if I'm doing something wrong.) The insert generates all of 6Mb of data, and my rollback segments consume this w/o any trouble. My CPU and I/O go up to 100% each time - yikes! I executed this test many times - and it resulted in the same performance every time.

Here's the code

CREATE TABLE TMP
(
X INTEGER,
Y INTEGER
)
LOGGING
NOCACHE
NOPARALLEL;
CREATE OR REPLACE TRIGGER TMP_INSERT BEFORE INSERT ON TMP FOR EACH ROW
BEGIN
SELECT
tmp_SEQ.NEXTVAL INTO :NEW.x
FROM DUAL;
END;
/

DROP SEQUENCE TMP_SEQ; CREATE SEQUENCE TMP_SEQ
START WITH 5636113
MAXVALUE 1E27
MINVALUE 1
NOCYCLE
CACHE 10000
NOORDER; insert into tmp values (1)
/

insert into tmp select * from tmp
/

[do this until you have about 525K rows]

I then :
1) created table tmp1 as select y from tmp

2) truncate tmp

3) insert into tmp (y) select y from tmp1

TIA, JB Received on Mon Nov 08 2004 - 15:09:57 CST