Comments below ... ----------------- Ron Reidy Senior DBA Array BioPharma, Inc.
-----Original Message----- From: Helck, Timothy [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 7:52 AM To: Jared Still; Kipp, James Cc: Reidy, Ron; Jim; DBI List Subject: RE: Best Way to Auto Increment with Oracle It's not always convenient to use triggers to assign the primary key. Sometimes we want to know the primary key of a new record so we can use it to insert rows into a dependent table. [rr] Look at using the 'RETURNING' clause. Am I right to think that the following is nearly as efficient as a trigger-based approach? insert into customer(customer_id, first, last) values(customer_id_seq.nextval, 'Homer', 'Simpson'); insert into address(address_id, customer_id, street, town) values(address_id_seq.nextval, customer_id_seq.currval, '742 Evergreen Tr.', 'Springfield'); [rr] This will not work. What happens when there are multiple processes running an insert into customer at the same time. Sequences are not transactional. Or is there a better way to do this? Tim Helck -----Original Message----- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: Monday, July 05, 2004 9:45 PM To: Kipp, James Cc: 'Reidy, Ron'; Jim; DBI List Subject: RE: Best Way to Auto Increment with Oracle 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 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.
