Home » SQL & PL/SQL » SQL & PL/SQL » Using Sequence.NEXTVAL from DUAL versus in INSERT statment
Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581528] Mon, 08 April 2013 10:59 Go to next message
newbiefm2012
Messages: 7
Registered: December 2012
Junior Member
I am trying to understand the difference between using sequence.NEXTVAL from DUAL as against using it direclty in an INSERT statment. Is one way efficient over another? If yes, can you please explain why? I have the sample code below:

--Sequence Creation
CREATE SEQUENCE SEQ_ID START WITH 1 MINVALUE 1  NOCYCLE  CACHE 500  NOORDER;

--Table1 Creation
Create table TABLEA (COL1 number, COL2 varchar2(10),
constraint COL1_PL primary key (COL1));

--Table2 Creation
Create table TABLEB(COL3 number);
alter table TABLEB add constraint COL1_FK foreign key(COL3) references TABLEA(COL1);

-- Option1 - Using sequence.NEXTVAL from DUAL

DECLARE
v_seq_num NUMBER;
BEGIN

SELECT SEQ_ID.NEXTVAL INTO v_seq_num FROM DUAL;

INSERT INTO TABLEA (COL1, COL2) VALUES (v_seq_num, 'test');
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);

END;

-- Option2 - Using sequence.NEXTVAL in INSERT USING RETURNING INTO clause
DECLARE
v_seq_num NUMBER;

BEGIN

INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test') RETURNING COL1 INTO v_seq_num;
INSERT INTO TABLEB (COL3) VALUES (v_seq_num);

END;
Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581529 is a reply to message #581528] Mon, 08 April 2013 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 23055
Registered: January 2009
Senior Member
what does EXPLAIN PLAN & SQL_TRACE show happens?
Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581530 is a reply to message #581528] Mon, 08 April 2013 11:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is one way efficient over another?


Maybe at the microsecond level not at man one.

Regards
Michel
Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581553 is a reply to message #581530] Mon, 08 April 2013 20:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8005
Registered: November 2002
Location: California, USA
Senior Member
Please see the excerpt from the online Performance Tuning Guide below.

http://docs.oracle.com/cd/E11882_01/server.112/e16638/sql_overview.htm#PFGRF94834

16.5.9.2 Use DML with RETURNING Clause

When appropriate, use INSERT, UPDATE, or DELETE... RETURNING to select and modify data with a single call. This technique improves performance by reducing the number of calls to the database.
Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581579 is a reply to message #581553] Tue, 09 April 2013 01:51 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
Is there a reason, you can't use SEQ_ID.CURRVAL ?
DECLARE
v_seq_num NUMBER;

BEGIN

  INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test');
  INSERT INTO TABLEB (COL3) VALUES (SEQ_ID.CURRVAL);

END;
Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581590 is a reply to message #581579] Tue, 09 April 2013 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you can test it and tell us the result. Wink

regards
Michel
Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581674 is a reply to message #581579] Tue, 09 April 2013 13:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8005
Registered: November 2002
Location: California, USA
Senior Member
_jum wrote on Mon, 08 April 2013 23:51
Is there a reason, you can't use SEQ_ID.CURRVAL ?
DECLARE
v_seq_num NUMBER;

BEGIN

  INSERT INTO TABLEA (COL1, COL2) VALUES (SEQ_ID.NEXTVAL, 'test');
  INSERT INTO TABLEB (COL3) VALUES (SEQ_ID.CURRVAL);

END;


What if there are multiple sessions accessing the same sequence and somebody else's select of nextval comes between another person's select of nextval and currval? Whether or not this matters might depend on the situation.


Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581677 is a reply to message #581674] Tue, 09 April 2013 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CURRVAL is private to a session, it is the value you got from your previous NEXTVAL.
Each session that used the sequence has its own CURRVAL.

Regards
Michel

[Updated on: Tue, 09 April 2013 13:50]

Report message to a moderator

Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581680 is a reply to message #581677] Tue, 09 April 2013 16:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8005
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Tue, 09 April 2013 11:47
CURRVAL is private to a session, it is the value you got from your previous NEXTVAL.
Each session that used the sequence has its own CURRVAL.

Regards
Michel



Oops! Thanks for the correction. In that case, I am thinking, like Jum suggested, that maybe using currval would be just as good as using returning into or perhaps better.


Re: Using Sequence.NEXTVAL from DUAL versus in INSERT statment [message #581690 is a reply to message #581680] Wed, 10 April 2013 00:19 Go to previous message
Michel Cadot
Messages: 59791
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think so too but the difference is a matter of microseconds.

Regards
Michel
Previous Topic: insert Rupee(currency) symbol
Next Topic: regexp_replace multiple replace - at the end of the string
Goto Forum:
  


Current Time: Wed Nov 26 10:26:38 CST 2014

Total time taken to generate the page: 0.17329 seconds