Re: Best Way to Auto Increment with Oracle

From: Peter J. Holzer (hjp_at_wsr.ac.at)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 17:46:28 +0200
To: dbi-users@perl.org

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

Relevant Pages

  • 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: 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
    ... 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
    ... No SP or trigger to fire and no extra network round trips? ... The statement is executed and the fetch occurs. ... You then execute the insert statement. ... > If you use a BEFORE INSERT trigger to fetch the sequence, ...
    (perl.dbi.users)
  • RE: Best Way to Auto Increment with Oracle
    ... >> have here is a soft parse, but non the less, you are using ... >> the CPU for this. ... I think I will stay with the Before insert trigger. ... from a before insert trigger via a sequence. ...
    (perl.dbi.users)