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 09:14:25 -0600
To: "Jim" <nep_tuna@yahoo.com>, <dbi-users@perl.org>

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.

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

-----Original Message-----
From: Jim [mailto:nep_tuna@yahoo.com]
Sent: Wednesday, June 16, 2004 6:21 AM
To: dbi-users@perl.org
Subject: Best Way to Auto Increment with Oracle

Hi
I am looking for suggestions on the best way to auto
increment a key column in a oracle table. I know I can
do this inside of Oracle by creating a sequence and
then a trigger. BUT, is it better to make DBI do this
when I insert the data? If so, how would I go about
it?
Thanks

                
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail

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: Best Way to Auto Increment with Oracle
    ... or just bundle up the two statements in one execution. ... The statement is executed and the fetch occurs. ... Best Way to Auto Increment with Oracle ... > message, or notify us by ...
    (perl.dbi.users)
  • 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: PL/SQL job failing - extents?
    ... and Informix background - Oracle - total newbie. ... Got a PL/SQL run which fails fairly regularly with a fetch out of sequence ... I asked our Oracle bods whether this may be the case, ...
    (comp.databases.oracle.misc)
  • RE: Best Way to Auto Increment with Oracle
    ... Best Way to Auto Increment with Oracle ... Doesn't an Oracle before insert trigger carry extra ... > insert time, you need to fetch a sequence, so you ... 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
    ... 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)