Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It there a performance impact when using the dual table to read a sequence' nextval over an direct insert?

I'm currently optimizing the performance in a large IT system and what I've found is that almost all sequence.nextval are read in the following way:

SELECT SOME_SQ.NEXTVAL INTO someID FROM DUAL;
insert into .... (someId)

I'm wondering whether the performance might suffer when using the way as described above over a standard:

insert into .... (some_sq.nexval, ...)

Thanks

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Note that 11g has support for sequence expressions directly in PL/SQL

Otherwise, from the Oracle Docs:

Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan.

So any performance gain will be slight. In the following tests I dropped and recreated the table and sequence between each execution:

testbed:

create sequence s;
create table t(id integer);

tests:

declare
  n integer;
begin
  for i in 1..100000 loop
    select s.nextval into n from dual;
    insert into t(id) values(n);
  end loop;
end;
/
--average about 5s

declare
  n integer;
begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval) returning id into n;
  end loop;
end;
/
--average about 4s

begin
  for i in 1..100000 loop
    insert into t(id) values(s.nextval);
  end loop;
end;
/
--average about 3s

You should probably rerun these tests or ones like them in you own environment.

share|improve this answer
    
+1 for supplying a test case –  a_horse_with_no_name Sep 2 '11 at 9:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.