RE: Best Way to Auto Increment with Oracle

From: Ron Reidy (Ron.Reidy_at_arraybiopharma.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 10:30:17 -0600
To: "David Goodman" <dtzgdman@yahoo.com>, <dbi-users@perl.org>

Set event 10046 and trace the execution.

I am reasonably sure any overhead associated with a trigger will not be as expensive as client side SQL*Net round trips to get sequences and the associated SQL*Net wait events while the client is waiting for the server, or while the server is waiting for the client.

-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.

-----Original Message-----
From: David Goodman [mailto:dtzgdman@yahoo.com]
Sent: Wednesday, June 16, 2004 9:59 AM
To: dbi-users@perl.org
Subject: Re: Best Way to Auto Increment with Oracle

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

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Relevant Pages

  • RE: Access Append Query to update Sequence Generator in Oracle
    ... Jerry Whittle, Microsoft Access MVP ... to be a Trigger for TASSIGNINTERVAL ... 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)
  • 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
    ... At insert time, you need to fetch a sequence, so you issue 'select seq.nextval from dual'. ... Best Way to Auto Increment with Oracle ... sender of the delivery error by replying to this message, or notify us by ...
    (perl.dbi.users)
  • Re: Hello, a question comparing sql server to Oracle
    ... Yes in oracle you create a sequence which is "outside" of the table ... so I don't want to code the trigger in pl/sql if there is a more ... Triggers do have some overhead. ...
    (comp.databases.oracle.server)