11 Replies Latest reply: May 7, 2012 11:10 AM by AlbertoFaenza RSS

    Recycled Sequence Numbers

    User9954330-Oracle
      Hi,
      We have a requirement of generating sequence numbers for a table and we need to recycle them if some of them is deleted.

      For example the following tabe Employee(Id,name)

      ID NAME
      1 XXX
      2 YYY
      3 ZZZ
      4 AAA

      The ID filed should have the next sequence number generated. When I call getnextID now, it should return 5
      ID NAME
      1 XXX
      2 YYY
      3 ZZZ
      4 AAA
      5 BBB

      Now Let us assume I delete 3. The contents of the table aare

      ID NAME
      1 XXX
      2 YYY
      4 AAA
      5 BBB

      Now when getNextID, 3 should be returned.
      How to achieve this in a best performing way in Oracle? Are there any patterns around it? Please note this should run in a multi user environment.

      Thanks,
      Rama
        • 1. Re: Recycled Sequence Numbers
          Karthick_Arp
          Why do you want to do such a thing? What is the potential benifit that you will get out of this approach. This looks like a bad design. Just let the sequence number to generate its own value.
          • 2. Re: Recycled Sequence Numbers
            User9954330-Oracle
            We use this generate identifiers for specific entities, where reuse of sequence numbers is mandatory.
            • 3. Re: Recycled Sequence Numbers
              Billy~Verreynne
              user9954330 wrote:
              We use this generate identifiers for specific entities, where reuse of sequence numbers is mandatory.
              No. Let me rephrase that for you. The reuse of sequence numbers is idiotic.

              It is not an Oracle issue. It is not a technical issue. It is a design issue.

              Show me your data model. And I will show why it is idiotic.
              • 4. Re: Recycled Sequence Numbers
                Karthick_Arp
                user9954330 wrote:
                We use this generate identifiers for specific entities, where reuse of sequence numbers is mandatory.
                Can you show us the existing code of Generate Identifier. Lets see how does it generates the sequence number.
                • 5. Re: Recycled Sequence Numbers
                  User9954330-Oracle
                  I just simplified the example with sequence number. But the column is used for identifying a connection between two locations. It totally consists 30 characters out of which 3 characters form the serial number. When a connection is removed, the existing serial number should be reused for new connection and new identifier should be generated with recycled serial number.
                  "
                  • 6. Re: Recycled Sequence Numbers
                    indra budiantho
                    1. When u delete the sequence, insert it into another table, say it T.
                    2. Use locking mechanism when select the record for T or just use it if you set the serial number as a key / unique (what u need is catch the exception).
                    • 7. Re: Recycled Sequence Numbers
                      Billy~Verreynne
                      user9954330 wrote:
                      But the column is used for identifying a connection between two locations. It totally consists 30 characters out of which 3 characters form the serial number.
                      And that is why this is a *<font color="Red">FAIL</font>*.

                      A connection is typically defined from entity A to entity B. Standard data modelling says that the connection entity will then be defined as follows:
                      CONNECTTION = ( UNIQUE_ENTITY_ID_FROM, UNIQUE_ENTITY_ID_TO )

                      A data model that defines that connection relationship as a single column of 30 characters that contains a complex data structure (serial number embedded in the value) is idiotic.

                      And yes, the term idiotic is the correct one to use.

                      Unless you can substantiate the +30-column-with-embedded-serial-number+ design for defining what should be a relationship for a basic vector?
                      • 8. Re: Recycled Sequence Numbers
                        User9954330-Oracle
                        I am sorry. This connection is not a normal entity to entity connection. I am referring to network connectivity here.
                        • 9. Re: Recycled Sequence Numbers
                          Billy~Verreynne
                          user9954330 wrote:
                          This connection is not a normal entity to entity connection. I am referring to network connectivity here.
                          A network connection provides connectivity between point A and point B - where these can be IP addresses (as in a typical <i>traceroute</i>), router interfaces, port switches and so on.

                          Network connectivity is exactly what our Oracle clusters and data models deal with (for networks with over 20,000 devices). So this is not exactly an unfamiliar topic.
                          • 10. Re: Recycled Sequence Numbers
                            User9954330-Oracle
                            OK. So we generate ConnectivtyID to the connection (Pipe). So we generate the identification for this Pipe with location end points and a serial number. When we delete the Pipe, we would like to reuse the serial number. Now that requirement is clear to you, can you suggest a soliution if you have one?
                            • 11. Re: Recycled Sequence Numbers
                              AlbertoFaenza
                              I think the answers were based on the fact that you call it incorrectly sequence.

                              Your ConnectivityID is more as a resource that could be recycled. I see some similarities to the GSM telecom market where a resource(i.e. a phone number or a SIM card no) can be recycled when is not active anymore (under certain conditions however).

                              Personally I would generate a table of resources having the resource number (ConnectivityID) and resource status.

                              To simplify it, suppose that you have column status with 2 possible values, 0=available, 1=not available.
                              When you want to allocate the resource number(ConnectivityId) you will set the status to 1.
                              When the record is deleted then status will be updated back to 0.

                              Recycling a ConnectivityID will mean searching in the table for the lowest number having status=0.

                              Of course you don't expect this to be as fast as a sequence but I think this is a different requirement, IMO.

                              Regards
                              Al