Re: Guaranteed Order With a Sequence / Trigger

A copy of this was sent to john_galt29_at_my-deja.com (if that email address didn't require changing) On Sun, 24 Oct 1999 02:57:04 GMT, you wrote:

>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,

No, it explicity does NOT.

> that there will never be a gap in the numbers?

No, it explicity does NOT.

There will be gaps and there will be 'apparent' out of order-ness conditions. (if you have N sessions inserting simultaneously, their numbers will 'interleave'. you will have rows with low sequence numbers that were committed AFTER some rows with higher sequence numbers). Sequences are useful as non-blocking, unique id generators -- nothing else.

> Or should I do
>something at the application level, like lock the table before doing an
>insert? I would prefer *NOT* to do that ...
>

Why do the rows need to be 'ordered' in insert order? Can you reconsider that. Or perhaps post what you are trying to accomplish at the end of the day -- not how you wish to accomplish it (eg: you are asking how to sequentially number the rows to achieve your goal. instead, post exactly what the goal is and ask how others might solve it....)

>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 - 08:37:38 CDT