RE: Best Way to Auto Increment with Oracle

From: Jared Still (jkstill_at_cybcon.com)
Date: 07/06/04 

To: "Kipp, James" <James.Kipp@mbna.com>
Date: Mon, 05 Jul 2004 18:44:32 -0700

On Wed, 2004-06-16 at 08:43, Kipp, James wrote:
> >
> > Your program connects via SQL*net to the DB. At insert time,
> > you need to fetch a sequence, so you issue 'select
> > seq.nextval from dual'. Seems harmless enough until ...
> >
> > 1. The select statement must be passed across the SQL*Net connection.
> > 2. The statement must be parsed. Maybe the worst you will
> > have here is a soft parse, but non the less, you are using
> > the CPU for this.
> > 3. The statement is executed and the fetch occurs.
> > 4. The result is passed back to your program.
> > 5. You bind the value (hopefully) to your statement handle.
> > 6. You then execute the insert statement.
> >
> > If you use a BEFORE INSERT trigger to fetch the sequence, you
> > will eliminate steps 1, 2, 3, 4, 5 from the flow.
> >
>
> Great point. I think I will stay with the Before insert trigger.
>
>

I've always liked the elegance of inserting primary key values
from a before insert trigger via a sequence. I still do it
sometimes, though now I prefer not to.

When done from a trigger, you must use the
'select seq.nextval from dual' statement to get the PK value.

This is a *major* scalability killer. Easy to prove if anyone
is interested.

Or just google for it, it has been discussed quite heavily on
at least one oracle list.

Jared

Relevant Pages

  • RE: Access Append Query to update Sequence Generator in Oracle
    ... There are two Triggers apparently for TASSIGNMENT and there doesn't appear ... to be a Trigger for TASSIGNINTERVAL ... SELECT COUNTINTO assignmentVariable10 ... Also committing should not make any difference to the sequence or trigger. ...
    (microsoft.public.access.queries)
  • Re: stepping midi sequence. one chord per trigger.
    ... I've done virtually all my MIDI work in BeOS, which is why I say my ... would be to create a MIDI sequence of just the desired note-ons, ... tempo and when triggers occur. ... order of 10 milliseconds per trigger. ...
    (comp.music.midi)
  • RE: Best Way to Auto Increment with Oracle
    ... What if some code in a trigger on the table did a NEXTVAL on the sequence? ... you are threading (say in Java. ... You then execute the insert statement. ...
    (perl.dbi.users)
  • RE: Access Append Query to update Sequence Generator in Oracle
    ... to be a Trigger for TASSIGNINTERVAL ... SELECT COUNTINTO assignmentVariable10 ... Also committing should not make any difference to the sequence or trigger. ... was that the Sequence Generator in Oracle remained at the last number PRIOR ...
    (microsoft.public.access.queries)
  • RE: Best Way to Auto Increment with Oracle
    ... Once a sequence number is generated, the sequence number is available only to the session that generated the number. ... Best Way to Auto Increment with Oracle ... I think I will stay with the Before insert trigger. ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)