 nextval and sysdate out of order [message #303239] |
Thu, 28 February 2008 09:01  |
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 #303265 is a reply to message #303248] |
Thu, 28 February 2008 09:56  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
