Re: SQL Performance Question

>hanks 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?

It has been my experience that if you can do a process completely in a SQL statement, that it is usually fast to do it that way rather than try to replicate the process in PL/SQL, but I imagine that it depends on the number of rows that are duplicated (ie, are there as many rows in the conversion tables as in MyTable?) If one is a lot smaller, it might be worth doing tricks with PL/SQL tables

What will probably increase the performance is to use the direct load option. It is a hint in the insert statement...

insert /*+append */ into table...

Look in the docs for details on what it does.

Dan Hekimian-Williams Received on Wed Oct 20 1999 - 00:00:00 CEST