Sequence Number issue in 11g RAC

[rchenna]

Hi All,

In our production, we have two nodes in the cluster. We use the sequence for one of the main table for primary key. Our application is expecting sequence number increments along with created date time stamp. Right now sequences are cached for each node and it creates problem for the application. We would not like to use NOCACHE option because it causes performance issue.

This is the current scenario -

Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 51 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 2 - Time Stamp 12:03

When I try to query based on the time stamp, primary should also go up. How do I achieve that?

Thanks in advance,
Rao

[rchenna]

To be very clear on what I would like to have, please consider the following example.

Without using NOCACHE option, I need to have the data in the following order.

Transaction #1 on Node 1 - Seq ID 1 - Time Stamp 12:01
Transaction #2 on Node 2 - Seq ID 2 - Time Stamp 12:02
Transaction #3 on Node 1 - Seq ID 3 - Time Stamp 12:03

In other words, sequence number should always increment along with the time.

[LKBrwn_DBA]

... Etc ...
Without using NOCACHE option, I need ...

Keep on dreaming...not possible without using NOCACHE option.

[gandolf989]

IMHO, You don't really need the sequence to be in the same order as the date stamp, it is not important or attainable, without the no cache. You can also try looking for a natural key in your data, since there is a lot of overhead in using sequences for insert heavy tables.