Re: Insert statement taking a very long time to complete

On Mar 20, 11:16 am, "gor..._at_panix.com" <gor..._at_panix.com> wrote:
> [I recently posted this on .server; I apologize for the double-
> posting.]
>
> I'm having some trouble with an SQL insert statement taking waaay too
> long to run. It's been suggested that maybe I'm using inefficient
> SQL, and that there might be a faster way to achieve the results.
>
> I have two tables with identical structure, and I want to copy all
> records that exist in table A but not in table B, into table B.
>
> Assuming that the structure is primary key "pk" and data fields "f1",
> "f2" and "f3", here is the SQL statement I'm using:
>
> insert into tableb(pk, f1, f2, f3)
> (select * from tablea where tablea.pk not in (select pk from
> tableb))
>
> This statement has worked well for small amounts of data, but when I
> tried on a table with approximately 200k records, it took forever to
> finish.
>
> A coworker with some SQL experience thinks that the "where" clause is
> being executed for *every* insertion. If so, this could certainly
> account for much of the slowdown.
>
> Anyone have any suggestions?
>
> --
> John Gordon A is for Amy, who fell down the stairs
> gor..._at_panix.com B is for Basil, assaulted by bears
> -- Edward Gorey, "The Gashlycrumb
> Tinies"

Well an explain plan would give you more information about how Oracle is actually processing the insert.

You could try changing the not in clause to a not exists which would perform the sub-select to check if the row alreadys exists once for each row in the driving select.

You could also look up the MERGE statement to see if you could update the existing rows and add the missing rows.

You could use a pl/sql block with an exception cluase that captures duplicate key errors then you would not need the check subquery. (This approach is not efficient, but I thought I would mention it as something you could test)

HTH -- Mark D Powell -- Received on Tue Mar 20 2007 - 10:34:23 CDT