Re: Guaranteed Order With a Sequence / Trigger

A copy of this was sent to "Matthew McPeak" <mcpeakm_at_email.msn.com> (if that email address didn't require changing) On Sun, 24 Oct 1999 10:54:13 -0400, you wrote:

>John,
>
>As others have said, using a sequence is not going to guarantee an ordered
>list of numbers without gaps.
>
>There is a DBMS_UTILITY.GET_TIME function. (I am a little fuzzy on the
>exact name, it might be something else close to that). This function will
>return the system time to 100ths of a second. You might try to have your
>application use it to insert the exact time of insert into the table.
>Suppose you call the column date_created.
>

careful -- the time returned is the hsecs from some arbitrary point in time and will wrap around to zero eventualy and on some platforms might even go negative before it wraps. It is useful for timing discrete events but not much else.....

>Then, write a query like this:
>
>UPDATE my_table mt1
>SET order_column = (
> SELECT count(*)
> FROM my_table mt2
> WHERE ( mt2.date_created < mt1.date_created OR
> (mt2.date_created =
>mt1.date_created AND
> mt2.id <= mt1.id) ) )
>
>
>That will update the order_column with an unbroken list of numbers that are
>in order according to date_created. This update could run in a
>statement_level POST_INSERT trigger on my_table if you want, but if my_table
>is big, the performance might suffer. There are ways around that though.
>Unfortunately, a row level trigger cannot work because my_table will be
>mutating at that point.
>
>Off-hand, I can't think of another way without locking the table.
>
>Hope this helps,
>
>Matt
>
>
>
>
>
>
>
>john_galt29_at_my-deja.com wrote in message <7utshv$jl7$1_at_nnrp1.deja.com>...
>>I would like to use a certain column in my table to perform some
>>calculations and as such I need to be sure of the numbers that are in
>>that column. Specifically I want that column to start with the value 1
>>and go up from there, incremented by 1 every time a record is added. I
>>am using the following sequence / trigger combo:
>>
>>CREATE SEQUENCE myTableSeq1 INCREMENT BY 1 START WITH 1 ORDER NOCACHE;
>>
>>CREATE OR REPLACE TRIGGER myTableT1 BEFORE INSERT ON myTable
>>FOR EACH ROW
>>BEGIN
>>SELECT myTableSeq1.nextval INTO :new.id FROM dual;
>>END;
>>
>>Does this ABSOLUTELY GUARANTEE that the sequence will NEVER be out of
>>order, that there will never be a gap in the numbers? Or should I do
>>something at the application level, like lock the table before doing an
>>insert? I would prefer *NOT* to do that ...
>>
>>Thanks for any help or comments!
>>John
>>
>>
>>
>>Sent via Deja.com http://www.deja.com/
>>Before you buy.
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Oct 24 1999 - 10:38:39 CDT