OraFAQ Forum: SQL & PL/SQL » SQL Sequence skips first value

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Sequence skips first value (Oracle 11g (Release 2) - 11.2.0.1.0, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Sequence skips first value [message #467429]

Fri, 23 July 2010 19:04

rkaula
Messages: 22
Registered: December 2005

Junior Member

For some reason Oracle Sequence is skipping the first value. Here is an example of script.

drop sequence tseq;
drop table xyz;

create table xyz
(sno integer);

create sequence tseq
start with 1;

insert into xyz values (tseq.nextval);

select * from xyz;

Now the runtime of the script in SQL Developer:

drop sequence tseq succeeded.
drop table xyz succeeded.
create table succeeded.
create sequence succeeded.
1 rows inserted
SNO
----------------------
2

Why is it skipping the starting value 1. Is there something new in 11g that is causing it to skip the first value. I have many scripts and they all are having errors due to this issue.

Thanks.

Report message to a moderator

Re: SQL Sequence skips first value [message #467430 is a reply to message #467429]

Fri, 23 July 2010 19:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have many scripts and they all are having errors due to this issue.
I suggest you fix your script; which should not depend any specific value by a sequence.

Report message to a moderator

Re: SQL Sequence skips first value [message #467431 is a reply to message #467430]

Fri, 23 July 2010 20:32

rkaula
Messages: 22
Registered: December 2005

Junior Member

So is this the way Oracle sequences will work from now (11g Release 2) onwards, ie. always skip first value or can never start with 1.

Report message to a moderator

Re: SQL Sequence skips first value [message #467432 is a reply to message #467431]

Fri, 23 July 2010 21:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>So is this the way Oracle sequences will work from now (11g Release 2) onwards, ie. always skip first value or can never start with 1.
Please post valid test case so we can reproduce what you describe.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided Test Case - http://www.orafaq.com/wiki/Test_case

What do you expect/desire to occur if/when FLASHBACK is done?

[Updated on: Fri, 23 July 2010 21:05]

Report message to a moderator

Re: SQL Sequence skips first value [message #467433 is a reply to message #467431]

Fri, 23 July 2010 21:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I cannot reproduce the case.

Report message to a moderator

Re: SQL Sequence skips first value [message #467434 is a reply to message #467433]

Fri, 23 July 2010 21:21

rkaula
Messages: 22
Registered: December 2005

Junior Member

I don't know what is meant by "reproduce what you desire." I have posted the error with a test script. What else should I do??

Let me explain: I recently installed Oracle 11g release 2 on a Windows 7 64-bit machine. Since I use Oracle database script files from a book for teaching purposes, I ran those scripts and started seeing errors. I have the same scripts running on previous Oracle versions (Oracle 9i, Oracle 10g, and Oracle 11g Release 1) and never had this error of the first sequence value getting skipped. If this error is specific to my machine, I guess I should uninstall release 2 and go back to release 1!!

Thanks for your help.

Report message to a moderator

Re: SQL Sequence skips first value [message #467435 is a reply to message #467434]

Fri, 23 July 2010 21:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I don't know what is meant by "reproduce what you desire."
Post all SQL statements using sqlplus via COPY & PASTE so we can see EXACTLY what you do & how Oracle responds.

>I have posted the error with a test script.
REALLY? What error has been posted?

>What else should I do??
Post content of test script

[Updated on: Fri, 23 July 2010 21:30]

Report message to a moderator

Re: SQL Sequence skips first value [message #467436 is a reply to message #467435]

Fri, 23 July 2010 21:44

rkaula
Messages: 22
Registered: December 2005

Junior Member

Maybe I didn't clear what I was requesting. The error is that the sequence is set to start at 1, but when the first insert statement with <sequence>.nextval is executed, the value inserted is not 1, but 2. It skips the starting value. Is it possible to make sure that the starting value is the first value inserted.

I had earlier copied/pasted from SQL Developer. Below is the SQL Plus runtime.

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 23 21:32:31 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: rajeev
Enter password:

Connected to:
Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop sequence tseq;

Sequence dropped.

SQL> drop table xyz;

Table dropped.

SQL>
SQL> create table xyz
2 (sno integer);

Table created.

SQL>
SQL> create sequence tseq
2 start with 1;

Sequence created.

SQL>
SQL> insert into xyz values (tseq.nextval);

1 row created.

SQL>
SQL> select * from xyz;

SNO
----------
2

SQL>

Report message to a moderator

Re: SQL Sequence skips first value [message #467437 is a reply to message #467436]

Fri, 23 July 2010 22:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6015.htm#i2067093

Doc. says as follows:

Creating a Sequence: Example The following statement creates the sequence customers_seq in the sample schema oe. 
This sequence could be used to provide customer ID numbers when rows are added to the customers table.

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;


The first reference to customers_seq.nextval returns 1000. The second returns 1001. 
Each subsequent reference will return a value 1 greater than the previous reference.

Below is what Oracle does for me.

SQL> @seq1
SQL> drop sequence tseq;

Sequence dropped.

SQL> drop table xyz;

Table dropped.

SQL> create table xyz  (sno integer);

Table created.

SQL> create sequence tseq  start with 1000;

Sequence created.

SQL> select * from user_sequences;

SEQUENCE_NAME			MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
TSEQ					1 1.0000E+28		1 N N	      20	1000

SQL> insert into xyz values (tseq.nextval);

1 row created.

SQL> select * from xyz;

       SNO
----------
      1001

The results I see differs from what is documented, IMO.

[Updated on: Sat, 24 July 2010 01:42] by Moderator

Report message to a moderator

Re: SQL Sequence skips first value [message #467439 is a reply to message #467437]

Sat, 24 July 2010 01:48

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> create table xyz (sno integer);

Table created.

SQL> create sequence tseq start with 1000;

Sequence created.

SQL> select * from user_sequences;
SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
TSEQ                                    1 1.0000E+27            1 N N         20        1000

1 row selected.

SQL> insert into xyz values (tseq.nextval);

1 row created.

SQL> select * from xyz;
       SNO
----------
      1000

1 row selected.

This is what I get with 9.2.0.8, 10.2.0.4 and 11.1.0.6 (sorry don't currently have 11.2).

Regards
Michel

Report message to a moderator

Re: SQL Sequence skips first value [message #467443 is a reply to message #467429]

Sat, 24 July 2010 05:13

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

might be a problem.

Or it might be correct behavior.

A sequence is not guaranteed to return values in any given order unless you use the order clause. Unless you use ORDER, then on your first retrieval from the sequence, the sequence is only required to give you a value that is in the current set of available values, not the first value of the sequence. Consider this:

create sequence kev1 start with 1 increment by 1 cache 10;

Given this sequence, the first set of values that will be made available for use will be the numbers 1 thru 10. When you grab your first sequence value, the sequence is only required to give you some number from 1 to 10. 2 is a number from 1 to 10 so the sequence is behaving correctly given its definition.

Now consider this sequence:

create sequence kev1 start with 1 increment by 1 cache 10 order;

The first set of values from this sequence will also be the numbers 1 thru 10, but this sequence is required to give out numbers in order so the first number given will be 1 the second number will be 2 and so on.

I am not saying that there is not a problem. Only that your description does not yet break any rules for sequences as I understand them.

Now try this:

create sequence kev1 start with 1 increment by 1 cache 10 maxvalue 10;

This sequence does not have the ORDER clause and so can give numbers out in any order. If you fetch from the sequence 10 times, do you get all 10 numbers 1 thru 10? Or do you get an error? If you can get all numbers from 1 thru 10 before you have an error, then you are OK if not then you have found a true bug.

select kev1.nextval from dual;
/
/
/
/
/
/
/
/
/

Good luck, Kevin

[Updated on: Sat, 24 July 2010 05:16]

Report message to a moderator

Re: SQL Sequence skips first value [message #467481 is a reply to message #467443]

Sat, 24 July 2010 13:52

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Kevin Meade wrote on Sat, 24 July 2010 06:13

A sequence is not guaranteed to return values in any given order unless you use the order clause.

Can you provide an example where sequence values are returned not in order on a single instance? AFAIK, and also according to docs:

Quote:

CREATE SEQUENCE

ORDER is necessary only to guarantee ordered generation if you are using Oracle Database with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.

ORDER in a sequence coordinates access to sequence between multiple nodes in RAC. It guarantees values are returned in order of requests made accross multiple instances of a database (RAC) by different nodes.

SY.

[Updated on: Sat, 24 July 2010 15:04]

Report message to a moderator

Re: SQL Sequence skips first value [message #467482 is a reply to message #467429]

Sat, 24 July 2010 14:59

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Yes really. As I said, sequences are not guaranteed to return values in order unless you use the ORDER clause.

For those who want to see docs with syakobson's quote, here is a link. You will find the following:

Quote:

ORDER
Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.

ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.

NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

About 20% the databases in my company run on RAC and we are in the process of consolidation so this percentage is going up for me over the next several years.

I take the OP at their word, that they did in fact see the behavior they described. If so, then the code I have provided will allow the OP to determine if they have a problem.

I will admit however that my post did lack detail. I thank you for bringing this to the attention of the community. My description of sequences though correct is incomplete and given that more than half of all databases are NOT running RAC, it was a mistake for me to leave these details out. Hopefully when others see the actual documentation page for 11g in the link they will understand.

My advice to everyone is if you want a sequence to be guaranteed to generate values in order, use the ORDER clause. As the future moves forward, use of RAC will increase because successful database grow user counts like rabbits make baby rabbits. Additionally using ORDER in the definition of a sequence when necessary provides knowledge about the index in the metadata even if you never expect your database to go to RAC some day.

Good luck, and thank you syakobson for this clarification of detail. Kevin

[Updated on: Sat, 24 July 2010 15:06]

Report message to a moderator

Re: SQL Sequence skips first value [message #467483 is a reply to message #467482]

Sat, 24 July 2010 15:27

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Kevin Meade wrote on Sat, 24 July 2010 15:59

I take the OP at their word, that they did in fact see the behavior they described. If so, then the code I have provided will allow the OP to determine if they have a problem.

I, on the contrary, am quite sceptical it is what OP describes. But I will assume it. Then, even if OP has RAC, I find it highly unlikely someone on some other node managed to select from a sequence between OP issuing DROP & CREATE sequence. And, BTW, if someone on some other node managed to do it, creating sequence with ORDER would not prevent a thing. Anyway, if what OP described is correct I'd be executing same script in SQL*Developer again to make sure it it is reproducible. If it is, I'd run it from SQL*Plus and if it is reproducible there too, I'd be looking for something like DDL triggers.

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #467484 is a reply to message #467429]

Sat, 24 July 2010 15:46

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I agree with you. The likelyhood of sequences skipping the first value is low even if they are running on RAC. And the value of ORDERed sequences is skeptical except possiblty for using a sequence as a form of time-stamp.

But the meaning of ORDER on a sequence is still clear, and the code I provided will allow the OP to determine if they have the problem they described regardless of it they are on RAC or not. If they can get an error from the last sequence I provided before they generate all 10 initial values then there is indeed a bug in their release of 11g.

Yet as you point out, the best money is riding on Operator Error.

I also restate my commentary. If you want a sequence to give values in true sequence, then use ORDER. It protects you from rare issues on RAC and provides additional metadata on your intent behind the sequence.

Kevin

[Updated on: Sat, 24 July 2010 16:00]

Report message to a moderator

Re: SQL Sequence skips first value [message #467485 is a reply to message #467484]

Sat, 24 July 2010 15:55

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Comments?

13:53:01 SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

13:53:44 SQL> @old/seq1
13:53:51 SQL> drop sequence tseq;

Sequence dropped.

13:53:51 SQL> drop table xyz;

Table dropped.

13:53:51 SQL> create table xyz	(sno integer);

Table created.

13:53:51 SQL> create sequence tseq  start with 1000;

Sequence created.

13:53:51 SQL> select * from user_sequences;

SEQUENCE_NAME			MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
TSEQ					1 1.0000E+28		1 N N	      20
       1000


13:53:51 SQL> insert into xyz values (tseq.nextval);

1 row created.

13:53:51 SQL> select * from xyz;

       SNO
----------
      1001

13:53:51 SQL>

Report message to a moderator

Re: SQL Sequence skips first value [message #467486 is a reply to message #467485]

Sat, 24 July 2010 16:09

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

BlackSwan wrote on Sat, 24 July 2010 16:55

Comments?

And same on other version(s)? I do not have 11.2, but looks OK on:

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table xyz (sno integer);

Table created.

SQL> create sequence tseq  start with 1000;

Sequence created.

SQL> insert into xyz values (tseq.nextval);

1 row created.

SQL> select * from xyz;

       SNO
----------
      1000

SQL>

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #467487 is a reply to message #467429]

Sat, 24 July 2010 16:14

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

OK, here's mine. Not in 11.1.0.7.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 rows selected.

SQL> drop sequence tseq;
drop sequence tseq
              *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> drop table xyz;
drop table xyz
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table xyz (sno integer);

Table created.

SQL> create sequence tseq  start with 1000;

Sequence created.

SQL> select * from user_sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
TSEQ                                    1 1.0000E+27            1 N N         20        1000

SQL> insert into xyz values (tseq.nextval);

1 row created.

SQL> select * from xyz;

       SNO
----------
      1000

1 row selected.

Kevin

Report message to a moderator

Re: SQL Sequence skips first value [message #467488 is a reply to message #467486]

Sat, 24 July 2010 16:15

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Must be a bug. Look at example in 11.2 SQL Reference http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_6015.htm#i2067093. It states fist call to sequence returns 1000 Smile

Smile

.

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #467489 is a reply to message #467488]

Sat, 24 July 2010 16:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Must be a bug.
I previously posted the Documented behavior.
I contend change in behavior fixes a bug.
If CURRVAL is 1000, then NEXTVAL is 1001; which is correctly returned.

Report message to a moderator

Re: SQL Sequence skips first value [message #467490 is a reply to message #467488]

Sat, 24 July 2010 16:21

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

syakobson wrote on Sat, 24 July 2010 17:15

Look at example in 11.2 SQL Reference

BlackSwan, could you run your test on NOCACHE sequence and see if you get 1001?

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #467491 is a reply to message #467429]

Sat, 24 July 2010 16:23

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Well as in all things, we have to read as much as we can in order to understand the overall perspective on things. All documentation takes some amount of "interpretation". For example:

Quote:

NOORDER
Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

I read this and I hear

1) NOORDER is the default for all sequences
2) NOORDER means exactly what is says, not guaranteed to generate numbes in order or request.

I suppose to BlackSwan I who provided such a good example, I would reply to last post that if I create my sequence to start with 1 I expect to be able to get 1 at some point in taking numbers off the first cached set.

However I also agree that the following does demonstrate a bug:

SQL> drop sequence xyz;

Sequence dropped.

SQL> create sequence xyz;

Sequence created.

SQL> select * from user_sequences where sequence_name = 'XYZ';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
XYZ                                     1 1.0000E+27            1 N N         20           1

1 row selected.

Having not yet selected from this sequence, why does it report in its metadata that the last number I selected was 1?

Kevin

[Updated on: Sat, 24 July 2010 22:28]

Report message to a moderator

Re: SQL Sequence skips first value [message #467493 is a reply to message #467490]

Sat, 24 July 2010 17:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member


15:32:51 SQL> @old/seq1
15:32:57 SQL> drop sequence tseq;

Sequence dropped.

15:32:57 SQL> drop table xyz;

Table dropped.

15:32:57 SQL> create table xyz	(sno integer);

Table created.

15:32:57 SQL> create sequence tseq  start with 1 NOCACHE;

Sequence created.

15:32:57 SQL> select * from user_sequences;

SEQUENCE_NAME			MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
TSEQ					1 1.0000E+28		1 N N	       0
	  1


15:32:57 SQL> insert into xyz values (tseq.nextval);

1 row created.

15:32:57 SQL> select * from xyz;

       SNO
----------
	 2

15:32:57 SQL>

Report message to a moderator

Re: SQL Sequence skips first value [message #467498 is a reply to message #467429]

Sat, 24 July 2010 22:52

ebrian
Messages: 2794
Registered: April 2006

Senior Member

I believe it has to do with the delayed segment creation in 11gR2.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table test;

Table dropped.

SQL> drop sequence test_seq;

Sequence dropped.

SQL> create table test (a1 number);

Table created.

SQL> create sequence test_seq start with 1;

Sequence created.

SQL> select count(*) from dba_segments where segment_name = 'TEST';

  COUNT(*)
----------
         0

SQL> insert into test values (test_seq.nextval);

1 row created.

SQL> select count(*) from dba_segments where segment_name = 'TEST';

  COUNT(*)
----------
         1

SQL> select * from test;

        A1
----------
         2

SQL>
SQL>
SQL>
SQL> drop table test;

Table dropped.

SQL> drop sequence test_seq;

Sequence dropped.

SQL> create table test (a1 number);

Table created.

SQL> create sequence test_seq start with 1;

Sequence created.

SQL> select count(*) from dba_segments where segment_name = 'TEST';

  COUNT(*)
----------
         0

SQL> insert into test values (1);

1 row created.

SQL> select count(*) from dba_segments where segment_name = 'TEST';

  COUNT(*)
----------
         1

SQL> truncate table test;

Table truncated.

SQL> select count(*) from dba_segments where segment_name = 'TEST';

  COUNT(*)
----------
         1

SQL> insert into test values (test_seq.nextval);

1 row created.

SQL> select * from test;

        A1
----------
         1


SQL>
SQL>
SQL>
SQL> drop table test;

Table dropped.

SQL> drop sequence test_seq;

Sequence dropped.

SQL> create sequence test_seq start with 1;

Sequence created.

SQL> select test_seq.nextval from dual;

   NEXTVAL
----------
         1

Report message to a moderator

Re: SQL Sequence skips first value [message #467500 is a reply to message #467498]

Sat, 24 July 2010 23:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

NOTE behavior of "select test_seq.nextval from dual;" between runs

21:03:16 SQL> @seq1
21:03:20 SQL> drop sequence tseq;

Sequence dropped.

21:03:20 SQL> drop table xyz;

Table dropped.

21:03:20 SQL> create table xyz	(sno integer);

Table created.

21:03:20 SQL> create sequence tseq  start with 1 NOCACHE;

Sequence created.

21:03:20 SQL> select * from user_sequences;

SEQUENCE_NAME			MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
TSEQ					1 1.0000E+28		1 N N	       0
	  1


21:03:20 SQL> select tseq.nextval from dual;

   NEXTVAL
----------
	 1

21:03:20 SQL> insert into xyz values (tseq.nextval);

1 row created.

21:03:20 SQL> select * from xyz;

       SNO
----------
	 3

21:03:20 SQL> drop sequence tseq;

Sequence dropped.

21:03:20 SQL> drop table xyz;

Table dropped.

21:03:20 SQL> create table xyz	(sno integer);

Table created.

21:03:20 SQL> create sequence tseq  start with 1 NOCACHE;

Sequence created.

21:03:20 SQL> insert into xyz values (tseq.nextval);

1 row created.

21:03:21 SQL> select tseq.nextval from dual;

   NEXTVAL
----------
	 3

21:03:21 SQL> select * from xyz;

       SNO
----------
	 2

21:03:21 SQL>

Report message to a moderator

Re: SQL Sequence skips first value [message #468702 is a reply to message #467498]

Sun, 01 August 2010 12:51

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

ebrian wrote on Sat, 24 July 2010 20:52

I believe it has to do with the delayed segment creation in 11gR2.

Yes, I believe the run below confirms that. With deferred_segment_creation set to the default of true, the first value is 2, but with deferred_segment_creation set to false, the first value is 1.

SCOTT@orcl_11gR2> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl_11gR2> create table xyz (sno integer)
  2  /

Table created.

SCOTT@orcl_11gR2> create sequence tseq start with 1 nocache
  2  /

Sequence created.

SCOTT@orcl_11gR2> insert into xyz values (tseq.nextval)
  2  /

1 row created.

SCOTT@orcl_11gR2> select * from xyz
  2  /

       SNO
----------
         2

1 row selected.

SCOTT@orcl_11gR2> drop sequence tseq
  2  /

Sequence dropped.

SCOTT@orcl_11gR2> drop table xyz
  2  /

Table dropped.

SCOTT@orcl_11gR2> alter system set deferred_segment_creation=false
  2  /

System altered.

SCOTT@orcl_11gR2> create table xyz (sno integer)
  2  /

Table created.

SCOTT@orcl_11gR2> create sequence tseq start with 1 nocache
  2  /

Sequence created.

SCOTT@orcl_11gR2> insert into xyz values (tseq.nextval)
  2  /

1 row created.

SCOTT@orcl_11gR2> select * from xyz
  2  /

       SNO
----------
         1

1 row selected.

SCOTT@orcl_11gR2> drop sequence tseq
  2  /

Sequence dropped.

SCOTT@orcl_11gR2> drop table xyz
  2  /

Table dropped.

SCOTT@orcl_11gR2>

Report message to a moderator

Re: SQL Sequence skips first value [message #468703 is a reply to message #468702]

Sun, 01 August 2010 12:57

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Interesting, this seems to indicate that Oracle tries the insert, it fails because there is no segment, create the segement then restart the insert from the beginning.

Regards
Michel

Report message to a moderator

Re: SQL Sequence skips first value [message #468705 is a reply to message #468703]

Sun, 01 August 2010 13:21

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Wow, you guys are so cool to see these things.

Kevin

Report message to a moderator

Re: SQL Sequence skips first value [message #468706 is a reply to message #468703]

Sun, 01 August 2010 13:41

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel Cadot wrote on Sun, 01 August 2010 13:57

Interesting, this seems to indicate that Oracle tries the insert, it fails because there is no segment, create the segement then restart the insert from the beginning.

Indeed, and I don't understand why. GTT is, in a sense, similar. There is no segment before first insert within a session or after commit, however select is not repeated:

SQL> create sequence s;

Sequence created.

SQL> create global temporary table test(x number);

Table created.

SQL> select  count(*)
  2    from  v$tempseg_usage
  3    where session_addr = (
  4                          select  saddr
  5                            from  v$session
  6                            where audsid = sys_context('userenv','sessionid')
  7                         )
  8  /

  COUNT(*)
----------
         0

SQL> insert
  2    into  test
  3    select  s.nextval
  4      from  dual
  5      connect by level <= 3
  6  /

3 rows created.

SQL> select  count(*)
  2    from  v$tempseg_usage
  3    where session_addr = (
  4                          select  saddr
  5                            from  v$session
  6                            where audsid = sys_context('userenv','sessionid')
  7                         )
  8  /

  COUNT(*)
----------
         1

SQL> select  *
  2    from  test
  3  /

         X
----------
         1
         2
         3

SQL> commit
  2  /

Commit complete.

SQL> select  count(*)
  2    from  v$tempseg_usage
  3    where session_addr = (
  4                          select  saddr
  5                            from  v$session
  6                            where audsid = sys_context('userenv','sessionid')
  7                         )
  8  /

  COUNT(*)
----------
         0

SQL>

So what can happen in creating initial segment that can affect SELECT results and therefore it has to be restarted?

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #468707 is a reply to message #468706]

Sun, 01 August 2010 13:50

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is not the same type of segment, this is new code, so this is not bug free (I think we can see it as a bug).

Regards
Michel

Report message to a moderator

Re: SQL Sequence skips first value [message #468712 is a reply to message #468707]

Sun, 01 August 2010 13:55

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

I know it is different type of segment, that is why I wrote GTT is, in a sense, similar. In both cases you have no segment at the time of insert - that is the similarity I had in mind.

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #468716 is a reply to message #468712]

Sun, 01 August 2010 14:05

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

I don't have access to metalink or whatever they call it nowadays, but for those of you who do, I found references to 1050193.1 about a bug with deferred segment creation and sequences skipping the first value in 11gR2.

Report message to a moderator

Re: SQL Sequence skips first value [message #468717 is a reply to message #468716]

Sun, 01 August 2010 14:16

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Barbara Boehmer wrote on Sun, 01 August 2010 15:05

I don't have access to metalink or whatever they call it nowadays, but for those of you who do, I found references to 1050193.1 about a bug with deferred segment creation and sequences skipping the first value in 11gR2.

And response from Oracle was as in many cases lately "is is not a bug - it is a feature":

Quote:

After dev investigating the issue they concluded that is normal behavior given the default setup of 11.2.

SY.

[Updated on: Sun, 01 August 2010 14:17]

Report message to a moderator

Re: SQL Sequence skips first value [message #468720 is a reply to message #468717]

Sun, 01 August 2010 15:38

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The Metalink note describes exactly what I suspected, it was a shame that Oracle closes that as not a bug saying "Subsequently, the statement is internally retried and the operands are evaluated again." and "But you should not rely on continuity of the values from a sequence" because it does not happen for a user defined function:

SQL> create table xyz (id integer, val integer);

Table created.

SQL> create or replace package mypkg
  2  as
  3    function inc return integer;
  4  end;
  5  /

Package created.

SQL> create or replace package body mypkg
  2  as
  3    mypkg_val integer := 0;
  4    function inc return integer
  5    is
  6    begin
  7      mypkg_val := mypkg_val + 1;
  8      return mypkg_val;
  9    end;
 10  end;
 11  /

Package body created.

SQL> insert into xyz values (0, mypkg.inc());

1 row created.

SQL> select * from xyz;
        ID        VAL
---------- ----------
         0          1

1 row selected.

So the call to the package function is clearly not reevaluated.

Regards
Michel

Report message to a moderator

Re: SQL Sequence skips first value [message #468721 is a reply to message #468720]

Sun, 01 August 2010 15:51

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And things get worse when you call the sequence inside a custom function:

SQL> create table xyz (id integer, val integer);

Table created.

SQL> create sequence tseq start with 1 nocache;

Sequence created.

SQL> create or replace function f
  2  return integer
  3  is
  4  begin
  5    return tseq.nextval;
  6  end;
  7  /

Function created.

SQL> insert into xyz values (1, f());

1 row created.

SQL> select * from xyz;
        ID        VAL
---------- ----------
         1          1

1 row selected.

Then now 2 equivalent codes are no more equivalent and Oracle says this is not a bug!

Regards
Michel

Report message to a moderator

Re: SQL Sequence skips first value [message #468722 is a reply to message #468721]

Sun, 01 August 2010 16:22

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel,

I didn't have sequence in mind. Assume long running query where it takes long time to get to first row fetch. Now CTAS or INSERT SELECT with such query might take significantly longer. And I am not sure what so "big" about deferred segment creation (besides advertizing new feature) that Oracle made it default behavior.

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #468723 is a reply to message #468722]

Sun, 01 August 2010 16:28

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Anyway, I just set DEFERRED_SEGMENT_CREATION = FALSE on my db.

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #468724 is a reply to message #468723]

Sun, 01 August 2010 17:12

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Hmm... this makes me wonder. The same issue may actually exist for any row insert using a sequence number generator when ever a new segment needs to be allocated. Maybe someone could check that too?

Kevin

Report message to a moderator

Re: SQL Sequence skips first value [message #468725 is a reply to message #468724]

Sun, 01 August 2010 17:35

Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA

Senior Member

Kevin Meade wrote on Sun, 01 August 2010 18:12

The same issue may actually exist for any row insert using a sequence number generator when ever a new segment needs to be allocated.

Not sure what you mean. Segment (along with initial extent) is created once per table. Then it is next extent that is allocated when needed.

SY.

Report message to a moderator

Re: SQL Sequence skips first value [message #468726 is a reply to message #468725]

Sun, 01 August 2010 17:38

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I am just curious if this is a SEGMENT related issue indicating perhapes that the behavior is related to dictionary management, or if it is an EXTENT related issue indicating that is is an already existing space allocation error behavior.

Kevin

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	Unused Variables in a Procedure
Next Topic:	simplify the query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 20 07:19:39 CDT 2024