On Wed, 2004-06-16 at 08:43, Kipp, James wrote: > > > > 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. > > > > Great point. I think I will stay with the Before insert trigger. > >
I've always liked the elegance of inserting primary key values from a before insert trigger via a sequence. I still do it sometimes, though now I prefer not to. When done from a trigger, you must use the 'select seq.nextval from dual' statement to get the PK value. This is a *major* scalability killer. Easy to prove if anyone is interested. Or just google for it, it has been discussed quite heavily on at least one oracle list. Jared
