2 Replies Latest reply: Feb 14, 2013 11:22 AM by rp0428 RSS

    ORDER and NORDER clause in Sequence creation

    greeny
      DB Version:11.2.0.3

      Read about ORDER and NOORDER clause in Sequence creation in 11.2 documentation

      http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_6015.htm

      Can anyone show me an example on how sequence behaves differently with ORDER and NORDER ?
        • 1. Re: ORDER and NORDER clause in Sequence creation
          P.Forstmann
          Please read following blog http://www.pythian.com/blog/sequences-in-oracle-10g-rac/.
          • 2. Re: ORDER and NORDER clause in Sequence creation
            rp0428
            >
            DB Version:11.2.0.3

            Read about ORDER and NOORDER clause in Sequence creation in 11.2 documentation

            http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_6015.htm

            Can anyone show me an example on how sequence behaves differently with ORDER and NORDER ?
            >
            Are you using RAC? If not then your sequences will be generated in order whether you use ORDER or the default NOORDER.

            As that doc link text says
            >
            ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
            >
            You can easily imagine your own example.

            Have you ever seen a roll of numbered tickets? You know, the kind of ticket they hand out to people when they offer a prize drawing at a meeting or event? You enter the event, pay your money and someone hands you a ticket with a number on it.

            If there is only ONE roll of tickets and only ONE person handing out tickets then the tickets will, obviously, get handed out in order.

            But if there is only ONE roll of tickets but TWO people that need to hand them out you have a problem. So you tear off a set of 50 or 100 tickets and give them to the second person to hand out. So person #1 has a roll of tickets that now start with number 101 and person #2 has 100 tickets numbered 1 to 100.

            They each hand out their tickets to people in their line. Now 200 people show up, get in line and start entering.

            Will the tickets get handed out in order from 1 to 200? Of course not, Line #2 may move faster than line #1 because some old lady in line #1 dropped her purse and made everyone behind her wait until she picked it up. Meanwhile person #2 handed out ALL of their tickets. Once the lady picked up her purse and got out the way person #1 handed out the rest of their tickets.

            Welcome to RAC! Each node of RAC gets their own 'cache' of tickets (sequence numbers). If you create a sequence using CACHE 100 each node gets a block of 100 sequence numbers. When they run out of 100 numbers they go back and get another block of 100 numbers.

            Since each node is doing different things they use their numbers up at different rates. The numbers will NOT be issued in order.

            If you use ORDER you force every RAC node to use the SAME cache of 100 numbers. Obviously that is NOT a good thing.

            Does that example help explain the difference between ORDER and NOORDER? And does it explain why you do NOT want to use ORDER unless absolutely necessary? It serializes the usage of sequence numbers. Serialization is not scalable.