Time to put another myth to bed.
Way back in Oracle 9 (I think) when referencing a sequence from PL/SQL the requirement of getting a sequence value using a SELECT statement was lifted. Thus instead of coding
SELECT MYSEQUENCE.NEXTVAL INTO MYVARIABLE FROM DUAL
you could simply write
MYVARIABLE := MYSEQUENCE.NEXTVAL ;
However, it is important to realise that this was done as a developer convenience, that is, to make the job of code writing easier, not some fundamental shift in the way the PL/SQL engine retrieves a sequence value. Sequences are, and remain, a facet of the SQL engine not the PL/SQL engine. Many of us have had the mantra of “avoid context switches between PL/SQL and SQL” drummed into us over the years, and thus we were tempted to believe that no longer seeing a SELECT keyword in front of our sequence retrieval meant some new found efficiencies. That is easily disproved with the following simple test.
SQL> create sequence seq cache 4000000;
Sequence created.
SQL> set timing on
SQL> declare
2 x int;
3 begin
4 for i in 1 .. 1000000 loop
5 x := seq.nextval;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.99
SQL> declare
2 x int;
3 begin
4 for i in 1 .. 1000000 loop
5 select seq.nextval into x from dual;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.03
Perhaps the fractionally longer piece of source to compile/interpret is responsible for the extra few hundredths of a second, or it could just be margin of error on my home PC, but it’s relatively clear that there is no order of magnitude shift in performance by the removal of the SELECT. The reason there is almost no difference in performance is revealed with a trace.
=====================
PARSING IN CURSOR #2451697287744 len=47 dep=0 uid=109 oct=47 lid=109 tim=1176421882925 hv=1829639270 ad='7ff881811f10' sqlid='1xb2af5qhw536'
declare
x int;
begin
x := seq.nextval;
end;
END OF STMT
PARSE #2451697287744:c=495,e=494,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1176421882924
BINDS #2451959247872:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=e433c668 bln=22 avl=04 flg=05
value=334605
EXEC #2451959247872:c=44,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=4006480256,tim=1176421883398
FETCH #2451959247872:c=20,e=20,p=0,cr=8,cu=0,mis=0,r=0,dep=2,og=4,plh=4006480256,tim=1176421883424
CLOSE #2451959247872:c=1,e=1,dep=2,type=3,tim=1176421883437
=====================
PARSING IN CURSOR #2451959968000 len=36 dep=1 uid=109 oct=3 lid=109 tim=1176421883468 hv=3683614325 ad='7ff87881e5e0' sqlid='f1g1x7vdsyzmp'
SELECT SEQ.NEXTVAL FROM "SYS"."DUAL"
END OF STMT
PARSE #2451959968000:c=496,e=496,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=1418648832,tim=1176421883468
EXEC #2451959968000:c=10,e=10,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1418648832,tim=1176421883544
=====================
To reiterate, not needing the SELECT in your code is simply a coding convenience. Sequence retrieval in PL/SQL is still being done with a SELECT from DUAL – we just saved you the typing effort!
The real performance benefits for using sequences in PL/SQL is simple – review your need to “know” the sequence number before you perform a DML associated with it.
For example, code like this:
MYPK := MYSEQUENCE.NEXTVAL;
INSERT INTO MYTABLE VALUES (MYPK,...);
can be refactored to the following
INSERT INTO MYTABLE VALUES (MYSEQUENCE.NEXTVAL,...)
RETURNING PKCOL INTO MYPK;
which means no additional SQL call to get the sequence number.
Even if I forgot the RETURNING clause, there are still opportunities to avoid the extra call.
For example, a parent and child insertion
MYPARENTPK := MYSEQUENCE.NEXTVAL;
INSERT INTO MYPARENT VALUES (MYPARENTPK,...);
INSERT INTO MYCHILD ( .... PARENT_FK, ... ) VALUES (..., MYPARENTPK,...);
can refactored as
INSERT INTO MYTABLE VALUES (MYSEQUENCE.NEXTVAL,...)
INSERT INTO MYCHILD ( .... PARENT_FK, ... ) VALUES (..., (MYSEQUENCE.CURRVAL,...);
and yes, the CURRVAL is session specific, so sequence activity from other sessions it not going to corrupt your data.
Bottom line – Most of the time, the only time you probably need to reference a sequence, is in a SQL statement, not in PL/SQL code.
Learning is not a spectator sport 
Leave a reply to Stanislav Cancel reply