Re: Sequences?

FYI, although I am not touting MS Access as a rival platform to Oracle ;), I have found MS Access Autonumbers to be extremely fast, efficient, intuitive, and transaction-safe, just like the identity columns in many server based SQL dbs.

I've recently ported a large app to Oracle. To get the behaviour out of Oracle which is simply a column-attribute in other dbs I've had to create a load of sequences, and a matching set of triggers which check for incoming values, and use the sequence if none is supplied (sometimes you want to supply the value yourself, eg on a mass data upload), other dbs ignore the autonumber/identity if you supply one, moreover if the supplied value is greater than the current stored identity value the sequence gets automatically reset to max+1 for you. This cannot be achieved in Oracle so I've had to write an out-of-process SP which checks all the sequences against max(theircolvalue) and "create and replaces" the sequence with the new highest number if necessary. Someone has to remember to run this SP when necessary. Quite a lot of pain to achieve an effect that I get for free in every other db.

To promote the lack of autonumber/identity as an advantage of Oracle's superior technology as some have done is a ludicrous kind of "Oracle does it that way so it must be the best" kind of thinking. Religious.

Regards

Tom

> Your terminology is a little unclear.
>
> Oracle sequences avoid serialising transactions because the sequence fetch
> and increment takes place outside the current transaction so the
transaction
> does not have to complete before anyone else can access the sequence. I
> don't know enough about access to know if this is true for access or not.
>
> I believe it is almost certainly the case the Oracle serialises access to
> sequences, though without access to the source code it's difficult to
tell.
>
> I suspect that a sequence is stored in memory as a single number. The
> incrementing of this number has to be serialised.
>
> Caching does not affect the need for serialisation, but does affect how
long
> (on average) the fetch and increment takes because of the time taken to
> write the updated value to disk every <cache> fetches.
>
> damorgan <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:3C6BF176.546BA760_at_ci.seattle.wa.us...
> > And Oracle can cache in memory a number of numbers so that they do not
> serialize
> > transactions the way autonumbering does.
> >
> > Daniel Morgan
> >
> >
> >
> > Jim Kennedy wrote:
> >
> > > It's magic.
> > > Sequences are controlled by the database and doled out as you ask for
> them
> > > eg mysequence.nextval
> > > Jim
> > > "Bruce" <bghagy_at_attbi.com> wrote in message
> > > news:YZFa8.53731$Pz4.283117_at_rwcrnsc53...
> > > > What's the mechanism to keep the value unique? If another SELECT is
> run
> > > > before the first INSERT wouldn't that give you two equal values?
> > > >
> > > >
> > > > "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> > > > news:3C6A966C.5E02C7DF_at_ci.seattle.wa.us...
> > > > > There is no relationship between MS Access's autonumbering and a
> > > SEQUENCE.
> > > > The
> > > > > autonumering is part of the table and is only capable of
sequential
> > > > numbering.
> > > > > It has no flexibility, no programmability, and is strictly tied to
a
> > > > single
> > > > > table. All it is is the following code built in and hidden from
the
> end
> > > > user.
> > > > >
> > > > > SELECT MAX(numbering_field)
> > > > > INTO next_number
> > > > > FROM xyz;
> > > > >
> > > > > INSERT INTO xyz
> > > > > (numbering_field + 1, other_field1, other_field2)
> > > > > VALUES
> > > > > (next_number, someval1, someval2);
> > > > >
> > > > > Daniel Morgan
> > > > >
> > > > >
> > > > >
> > > > > Magnus Ytterstad wrote:
> > > > >
> > > > > > Not sure how MS Access autonumber works, but it does sound like
a
> > > > sequence
> > > > > > to me.
> > > > > >
> > > > > > To create a sequence use the following syntax
> > > > > >
> > > > > > CREATE SEQUENCE MYSEQUENCE INCREMENT BY 1 START WITH 1000;
> > > > > >
> > > > > > When you want to access the sequence and get the next number,
you
> just
> > > > issue
> > > > > > the command MYSEQUENCE.NEXTVAL, i.e.:
> > > > > >
> > > > > > INSERT INTO MYTABLE (NAME, DEPT, ID)
> > > > > > VALUES ('John', 'Sales', MYSEQUENCE.NEXTVAL);
> > > > > >
> > > > > > Hope this helps!
> > > > > > -magnus.
> > > > > >
> > > > > > Bruce <bghagy_at_attbi.com> wrote in message
> > > > > > news:gP3a8.39737$bh3.256829_at_rwcrnsc52.ops.asp.att.net...
> > > > > > > How would one go about implementing the MS Access autonmuber
> data
> > > type
> > > > in
> > > > > > > 8i? Are sequences the way to go and could you point me in the
> right
> > > > > > > direction.
> > > > > > >
> > > > > > >
> > > > >
> > > >
> > > >
> >
>
>
Received on Sat Feb 16 2002 - 05:17:48 CST