Oracle 11gR2 Exadata

I'm required to uniquely identify when records are created in time. Sequence caching means I cannot use a sequence-based ID and batching inserts means that all records inserted in one batch will have the same timestamp value (even using TIMESTAMP(9)). Akin to Twitter's since_id concept.

The best alternatives I've ideated so far

  • creating an additional sequence used for each unique timestamp
  • not batching inserts of the records to force a unique timestamp for each record
  • not caching the sequence, although there has been some discussion that this won't solve the problem under Exadata

Here's my requirement: I have an API that allows users to supply a sequence as a marker and request all records since that time. For example, they request 1000 records with a marker of 7 and they'll get 1000 records from my table with an ID greater than 1007. As an example let's say the numerically greatest ID of the returned 1000 records is 2045 so we return 2045 as the marker Later the clients request 1000 records with a marker of 2045 expecting to get the next batch of 1000 and a new marker.

Pretty straightforward way to allow them to get all of the records in whatever size works for them without missing any. However, due to sequence caching across multiple Exadata nodes, at the time the client requests 1000 records with a marker of 1007, a record with an ID of 2020 may not have been created. Therefore, when they do the next request using the marker of 2045, they will have missed record 2020 forever. Using the ID to get the timestamp of the associated record solves this, but then I must make sure to always insert records into the table individually to guarantee unique timestamps.


  • Not a way to get separate timestamps for individual records on a bulk/batch insert
  • Multiple nodes may cause insertion of records to be non-time-sequential even with NOCACHE on the sequence (e.g., a record with sequence value 180 could be written with a systimestamp greater than that of a record with sequence value 179)

Hopefully I just haven't hit on the correct terms to search for existing answers. I feel that this is a problem that should have been solved by some pattern(s) for years. I think Twitter has solved it...

  • Just thinking out loud here - maybe an autonomous transaction function in a trigger that updates :new.insert_time, or something along those lines?
    – Philᵀᴹ
    Commented Apr 24, 2013 at 15:08
  • (By that I mean a row level trigger that gets the current time stamp from an autonomous function called in the trigger).
    – Philᵀᴹ
    Commented Apr 24, 2013 at 15:16
  • 2
    when you bulk insert with sysdate or systimestamp the time is generated as of the beginning of the statement so oracle makes sure that all the records that were inserted in the same statement will have the same timestamp. if you use seq with cach on top of that as your unique identifier you should be fine.
    – haki
    Commented Apr 24, 2013 at 17:41
  • Another approach -- see rp0428's suggestion to augment the final digits of the timestamp: forums.oracle.com/forums/…
    – eebbesen
    Commented Apr 24, 2013 at 21:51
  • Here's an answer to the question 'How does Twitter generate IDs?'. Not the solution to my problem, however, since it doesn't guarantee commit-time ordering.
    – eebbesen
    Commented Jun 20, 2013 at 19:14

2 Answers 2


I recommend ordering by timestamp. As long as the only records with identical timestamps are inserted in the same transaction, you can use the sequence-based ID as a secondary ordering.

  • 1
    Here's how I implemented Tyler's solution:1. Client requests n records using the record ID I gave them for the last request (or 0 if they want to start from the beginning) as the marker 2. I use the ID to find the timestamp 3. Select records that meet one of the following conditions a) have a timestamp greater than the record associated with the ID b) have the same timestamp as the record associated with the ID and have an ID greater than the passed in ID
    – eebbesen
    Commented Apr 24, 2013 at 23:59

Well, as it turns out I needed to use a synchronous process to assign an id to each record after the initial insert was complete.

As was pointed out to me, what I was really looking for was a commit sequence. Unfortunately even timestamp would not provide that because it is generated at insert time, not at commit time. So there could be situations where a timestamp for one group of records (A) might before a second group of records (B) but group B could actually be committed to the database first. Then I'd not have solved the original issue.

In the end we used what is essentially a synchronized home-implemented sequence via a procedure that used table locking (on the 'sequence' table) and skip locks. We therefore don't have to slow down our insert process by single-threading it, but we're guaranteed an ID that is guaranteed to be in a commit order of when the ID was committed to the record.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.