Re: Guaranteed Order With a Sequence / Trigger

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:wAoTOFkKLtN3roYUuc2=Oaw9ArmW_at_4ax.com...
> 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.

Yes, I didn't think properly in my prev post. Do you really need to have them in order in the table?

Hmm... Also, if someone does a bunch of inserts and then ROLLBACK, you will get a gap in the numbers (even with an ORDER BY in a SELECT), because the sequence's current value cannot be reset.

Nothing else to suggest except locking. Received on Sun Oct 24 1999 - 10:54:40 CDT