Re: Insert statement taking a very long time to complete

From: <rodriguez.patricio_at_gmail.com>
Date: 20 Mar 2007 19:26:41 -0700
Message-ID: <1174444000.962506.65390@l75g2000hse.googlegroups.com>

On 20 mar, 11:58, "gor..._at_panix.com" <gor..._at_panix.com> wrote:
> 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"

Hi, did you try using "not exists" instead of "not in (select ...) " Example

---

INSERT /*+ APPEND */ INTO tableb
  SELECT ta.*
    FROM tablea ta
 WHERE NOT EXISTS ( SELECT 1 FROM tableb tb WHERE tb.pk = ta.pk).

Regards
Patricio Received on Tue Mar 20 2007 - 21:26:41 CDT