Re: renumbering the data in a column?

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 17 Jan 2009 11:15:55 +0100
Message-ID: <6tdpevFaf0sdU1_at_mid.individual.net>



On 16.01.2009 18:52, Mark D Powell wrote:
> On Jan 15, 6:39 pm, m..._at_pixar.com wrote:
>> Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>>
>>> On Jan 14, 4:00 pm, ddf <orat..._at_msn.com> wrote:
>>>> You may need to modify the code for your actual data, but it,
>>>> hopefully, gives you a push in the right direction.
>> Thanks David, just what I needed!
>>
>>> Mark, David's plan is fine providing the sequence is not a PK or UK
>>> referenced by a FK constraint, but why do you need to re-number the
>>> sequence column?  If the sequence field is a key then generated key
>>> values are arbitrary in nature and gaps should not matter.  The reason
>>> I am asking has more to do with how the value will be handled going
>>> forward rather than the 'fix' being made.
>> Ah, I should have explained that part.  It's part of our
>> film processing system, the seq is actually a queue position.
>> So it's an easy way to bump an emergency job to the front
>> of the queue.
>>
>> It's a PK, but processed items are deleted from the system
>> so there's always a gap at the front..

> Ok, but 15, 17, 26 became 1, 2, 3 so the relative order remained the
> same. If the queue logic processes in key order what is the real need
> to re-value the key? If you just want to show the relative queue
> position 1..n then you can do that in the query results via row_number
> or perhaps even using rownum of an ordered select, that is, select
> film, rownum as QueuePosition from (select film from table order by
> key)

Just guessing: maybe he is afraid of running out of sequence numbers or wants to limit the sequence numbers because the fields has a small size limit like NUMBER(4).

> If you need to change the processing order you could have the sequence
> increment by 2 so that holes exist to move lower ranked items into
> when you want to advance a lower ranked item.

Or use negative numbers for prepending.

> Obviously there may be more to the process that your initial post and
> minor followup explain so I just want to make sure you have considered
> your options and are no doing unnecessary work.

Another approach could be to use a TIMESTAMP (insertion timestamp by default) for ordering. Then no Oracle sequence would be needed and also no overflow could happen (in the next 100 years or so anyway). Downside is of course that a TIMESTAMP needs more space in the DB and does not guarantee uniqueness (i.e. two insertions in the same moment - millisecond or whatever the chosen TIMESTAMP precision is).

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
Received on Sat Jan 17 2009 - 04:15:55 CST

Original text of this message