How to determine the actual placement order of a database row?

Question

I have a multi-threaded process that inserts multiple records into one table. Insertions are performed in a stored procedure, the sequence being generated by a variable INTO, and this variable is later used internally INSERT.

Given that I am not executing mysequence.nextvalinternally INSERT, this makes me think that two parallel processes can capture a sequence in the same order, and then insert in the reverse order. If so, the sequence numbers will not reflect the true insertion order.

I also write sysdatein the column DATEfor each of my inserts, but I noticed that it often happens that the dates for the two records are the same, and I need to sort by serial number to break the connection. But, given the previous release, this does not guarantee the actual insertion order.

How to determine the absolute order of insertion into the database?

Answer 1

DATE data types only go over seconds, while TIMESTAMP milliseconds. Will you be able to solve this problem?

According to Oracle docs:

TIMESTAMP: , , , , fractional_seconds_precision - SECOND datetime. fractional_seconds_precision - 0 9. - 6. NLS_DATE_FORMAT NLS_TERRITORY . 7 11 , . , , , , , . , .

date :

: 1 , 4712 .. 31 9999 . .. NLS_DATE_FORMAT NLS_TERRITORY. 7 . datetime YEAR, MONTH, DAY, HOUR, MINUTE SECOND. .

, , , , , , .

Answer 2

:

create table ORDERTEST (
    ORDERID number not null ,
    COLA   varchar2(10) ,
    INSERTDATE date default sysdate,
    constraint ORDERTEST_pk primary key (orderid)
) ;

create sequence ORDERTEST_seq start with 1 nocycle nocache ;

insert into ORDERTEST (ORDERID, COLA, INSERTDATE)
                select ORDERTEST_SEQ.NEXTVAL , substr(OBJECT_NAME,1,10), sysdate 
                  from USER_OBJECTS 
                 where rownum <= 5; --just to limit results

select * 
  from ORDERTEST
 order by ORDERID desc ;

 ORDERID                COLA       INSERTDATE                
---------------------- ---------- ------------------------- 
5                      C_COBJ#    16-JUL-10 12.15.36        
4                      UNDO$      16-JUL-10 12.15.36        
3                      CON$       16-JUL-10 12.15.36        
2                      I_USER1    16-JUL-10 12.15.36        
1                      ICOL$      16-JUL-10 12.15.36  

:

insert into ORDERTEST (ORDERID, COLA, INSERTDATE)
            select ORDERTEST_SEQ.NEXTVAL , substr(OBJECT_NAME,1,10), sysdate 
              from USER_OBJECTS 
             where rownum <= 5; --just to limit results

select * 
  from ORDERTEST
 order by ORDERID desc ;

 5 rows inserted
ORDERID                COLA       INSERTDATE                
---------------------- ---------- ------------------------- 
10                     C_COBJ#    16-JUL-10 12.17.23        
9                      UNDO$      16-JUL-10 12.17.23        
8                      CON$       16-JUL-10 12.17.23        
7                      I_USER1    16-JUL-10 12.17.23        
6                      ICOL$      16-JUL-10 12.17.23     

Oralce Sequence : http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/views.htm#ADMIN020 " , , , , ". 1,2,3,4,5 ( → , )

, : http://forums.oracle.com/forums/thread.jspa?threadID=910428 " , , . NextVal ".

- , , , return:

declare 
x number ;
begin 
insert into ORDERTEST (ORDERID, COLA, INSERTDATE)
                values( ORDERTEST_SEQ.NEXTVAL , 'abcd', sysdate)
                returning orderid into x;

dbms_output.put_line(x);
end;

--11

, .

Answer 3

. , . , , , , . sysdate .

, . nextval . nextval , (, 1 1 3 4 7, 2 2 5 6 8), , .

, nextval , . "" , . , , . . nextval , , , .

Answer 4

, , , . , , , , , . , , , , .

ORDER BY, . - , (, ..) .

Answer 5

, - ora_rowscn .

[] , , - rowdependencies, , scn, . , .

Answer 6

, , , . , , (), - , ? , , ( ( RAC)), , , .

, , , , . :

• 1
• 2.
• 3.
• Transaction 2
• 1
• 3
• 3
• 1
• 2

1, 2, 3 - . , . timestamp , . - , , , ( ).

Answer 7

(a) (b) NEXTVAL INSERT.

That way, when you query a table, you can ORDER BY timestamp, id, which will actually be the order in which the rows were actually inserted.