Re: Best Way to Auto Increment with Oracle

From: David Goodman (dtzgdman_at_yahoo.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 08:59:21 -0700 (PDT)
To: dbi-users@perl.org

Doesn't an Oracle before insert trigger carry extra
performance overhead?

--- "Peter J. Holzer" <hjp@wsr.ac.at> wrote:
> On 2004-06-16 09:14:25 -0600, Reidy, Ron wrote:
> > Better? Maybe. Consider.
> >
> > 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.
>
> You can get the next value from a sequence in the
> insert statement (see
> my previous mail in this thread), so with or without
> the trigger, you
> have to parse (once) and execute (possibly many
> times) only the insert
> statement.
>
> The difference is:
>
> With a trigger, you hide the details of the sequence
> from the insert
> statement.
> This makes the insert statement simpler and more
> portable. OTOH, you
> don't know which value you just inserted (you can
> get at it with "select
> $sequence.currval from dual" but then you lose these
> advantages).
>
> hp
>
> --
> _ | Peter J. Holzer | Shooting the users in
> the foot is bad.
> |_|_) | Sysadmin WSR / LUGA | Giving them a gun
> isn't.
> | | | hjp@wsr.ac.at | -- Gordon Schumacher,
> __/ | http://www.hjp.at/ | mozilla bug
> #84128
>

> ATTACHMENT part 2 application/pgp-signature

Relevant Pages

  • RE: Best Way to Auto Increment with Oracle
    ... to fetch a sequence, so you issue 'select seq.nextval from dual'. ... The statement is executed and the fetch occurs. ... You then execute the insert statement. ... my previous mail in this thread), so with or without the trigger, you ...
    (perl.dbi.users)
  • 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
    ... to fetch a sequence, so you issue 'select seq.nextval from dual'. ... The statement is executed and the fetch occurs. ... You then execute the insert statement. ... my previous mail in this thread), so with or without the trigger, you ...
    (perl.dbi.users)
  • 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)