SQL Performance Question
Date: Tue, 19 Oct 1999 15:53:24 GMT
Message-ID: <7ui45d$g2a$1_at_nnrp1.deja.com>
[Quoted] Thanks in advance for any advice...
Which of the following would provide better performance....
(Oracle 8, NT environment, being accessed from
Forms by calling a procedure in a stored package).
This is a specialize copy routine, which duplicates selected data in multiple tables by altering certain key information then inserting. There are at least 30,000 records being copied.
In the first scenario, a temp table is constructed with Old ID from copied records, and a conversion for New ID, generated from Sequence Generator. This table is used in all subsequent joins like so
insert (id, stuff...)
(select b.NewID, a.x, a.x, a.x....
from Mytable a, conversion table b,
where a.key = "various parms"
and a.ID = b.OldID)
Thus join is based on OLD ID, insert copies records using NEW ID.
In the second scenario, I used a PL/SQL
table to store the conversion NEW/OLD information,
and a function local to the stored package.
The index of the table is the OLD ID, the contents
of the table are the NEW IDs. The function is
Old_To_New(OldID). The table is loaded at the
time the new id is generated from sequence generator.
Then it is reference in the subsequent table copies.
Insert into mytable (id, stuff)
(select Old_To_New(OldID), stuff...
from Mytable
where ID in (select oldIDs
from "original table"
where "original condition")
(Original condition means the original key items
used on the first table, when the NEW IDs were first generated from the sequence generator based on old IDs)
This construct takes about 7 minutes for three tables equaling about 45,000 rows.
I am just wondering if in an attempt to save having a temp table, I have created a new performance problem....
Third,... maybe some form of saving ROWID's would be faster still?
Comments?
Thanks,
Robert Proffitt
Beckman Coulter
RTProffitt "AT" beckman "DOT" com
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Oct 19 1999 - 17:53:24 CEST