Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unanswered: Sequence Number issue in 11g RAC

    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?

    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.

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    This is never going to happen with sequences. This is a design issue with your application. An ID should never need to be sequential. An ID only ever needs to be unique (i.e. identifier). If you are using it this way, then you need to change your application.

    There are other ways of getting a sequential number accross nodes by rolling your own solution. However, this is never going to perform well.

    I would love to see the reasoning for requiring ordered ID's.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •