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:27:12 -0700 (PDT)
To: dbi-users@perl.org

Hello Ron:

Let me naively ask, why you would not use a stored
procedure to get rid of the network interaction? That
or just bundle up the two statements in one execution.

regards,

David

--- "Reidy, Ron" <Ron.Reidy@arraybiopharma.com> 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.
>
> -----------------
> 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
    ... 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: Repost: Cancel stored procedure using JDBC
    ... I have a Java application that spawns 4 different threads. ... the database stops execution and replies with an error message. ... When JDBC does not receive an IOException, Oracle Net may eventually time out ... The server-side internal driver runs in the ...
    (comp.lang.java.programmer)
  • Re: Limiting Large Result Sets
    ... Oracle 9i - Database ... Using query based approach, i was able to retreive all the million rows ... Fetch Size ... rows at a time from the database cursor. ...
    (comp.lang.java.programmer)
  • Re: Question for 10g enthusiasts
    ... force Oracle to use the better execution plan for all users. ... If the access is through private synonyms, the query will ...
    (comp.databases.oracle.server)
  • Re: row migaration
    ... The rowid remains the same as Oracle keeps a pointer to ... > means an extra IO is required to fetch the row when the fetch is via ... Like wise changing of a column that is part of the partion key ... > partition to the correct partition for its partition key. ...
    (comp.databases.oracle.server)