Using the special Dual table to read sequences

oraclesequence

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

Best Answer

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.

Related Question