Home » SQL & PL/SQL » SQL & PL/SQL » nextval and sysdate out of order (Oracle, 10g)
icon8.gif  nextval and sysdate out of order [message #303239] Thu, 28 February 2008 09:01 Go to next message
cornell
Messages: 6
Registered: February 2008
Location: Pennsylvania, USA
Junior Member
Short version:
nextval values are not in sysdate sequence, an insert at a later time has a lower sequence number. How can this be?

Long version:
I've a simple insert statement (I'm only including the fields of interest):
INSERT INTO SALARYDETAILS (DETAILS_ID,CREATE_DT, etc.
VALUES(SEQ_DETAILS_ID.nextval,sysdate, etc.

It is being executed from a Java application.

Searches of the project's Java and Stored Procedure code confirm that this is the only insert to the SALARYDETAILS table, and that there are no updates to the DETAILS_ID or CREATE_DT columns.

I find, in some cases, the sequence of DETAILS_ID (populated by nextval) does not correspond to the order of CREATE_DT (populated by sysdate). That is, order by DETAILS_ID and order by CREATE_DT have different results.

Here is the result of a select for a certain period, ordered by CREATE_DT:
DETAILS_ID          CREATE_DT                 
---------------------- ------------------------- 
3590                   26-FEB-08 08.32.23.000000000 AM 
3591                   26-FEB-08 08.51.42.000000000 AM 
3592                   26-FEB-08 08.52.41.000000000 AM 
3593                   26-FEB-08 09.06.27.000000000 AM 
3594                   26-FEB-08 09.28.25.000000000 AM 
3595                   26-FEB-08 09.33.17.000000000 AM 
3596                   26-FEB-08 09.38.51.000000000 AM 
3597                   26-FEB-08 09.45.16.000000000 AM 
3598                   26-FEB-08 09.48.59.000000000 AM 
3599                   26-FEB-08 09.52.26.000000000 AM 
3600                   26-FEB-08 09.53.40.000000000 AM 
3601                   26-FEB-08 09.54.27.000000000 AM 
3602                   26-FEB-08 09.54.52.000000000 AM 
3603                   26-FEB-08 09.56.28.000000000 AM 
3582                   26-FEB-08 10.50.25.000000000 AM 
3584                   26-FEB-08 10.53.21.000000000 AM 
3585                   26-FEB-08 10.54.08.000000000 AM 
3586                   26-FEB-08 11.13.46.000000000 AM 
3587                   26-FEB-08 12.03.21.000000000 PM 
3588                   26-FEB-08 01.56.27.000000000 PM 
3589                   26-FEB-08 03.18.13.000000000 PM 

Note that DETAILS_ID returned values in order (by time) from 3590 to 3603. Then it returned 3582 to 3589.

According to this data... it seems that we inserted DETAILS_ID 3603 at 09.56.28 and, 54 minutes later, inserted 3582, at 10.50.25.

Both I, and our DBA are puzzled about this.

If someone can shed some light on it, we'd appreciate it.

Thanks in advance
Cornell
Re: nextval and sysdate out of order [message #303248 is a reply to message #303239] Thu, 28 February 2008 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you specify ORDER on your sequence?
How to you get the sequence number? Do you get it in the INSERT statement?

Regards
Michel
Re: nextval and sysdate out of order [message #303265 is a reply to message #303248] Thu, 28 February 2008 09:56 Go to previous message
cornell
Messages: 6
Registered: February 2008
Location: Pennsylvania, USA
Junior Member
The nextval is in the insert statement.

And, apparently, we don't use ORDER:
   CREATE SEQUENCE  SEQ_SALDETAILS_ID
   MINVALUE 1 
   MAXVALUE 1.00000000000000E+27 
   INCREMENT BY 1 
   START WITH 3710 
   CACHE 20
   NOORDER
   NOCYCLE;

We also don't use order on any other sequences. Everything I'd read all says calling nextval increments and returns. Checking the Oracle Documentation on CREATE SEQUENCE, I find that ORDER is required to guarantee sequential order, but only when using "Real Application Clusters"

And... we're using Real Application Clusters. When I asked the DBA if we were, he said yes and it dawned on him that that might be the situation.

Anyway... this behaviour, while not intuitive, is correct.

Thank you
Cornell
Previous Topic: Multiple INSERTS
Next Topic: Update/insert logic results in duplicate rows.
Goto Forum:
  


Current Time: Tue Nov 05 14:14:21 CST 2024