Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Insert statement taking a very long time to complete

Re: Insert statement taking a very long time to complete

From: joel garry <joel-garry_at_home.com>
Date: 20 Mar 2007 13:17:11 -0700
Message-ID: <1174421831.008737.19520@l77g2000hsb.googlegroups.com>


On Mar 20, 7:58 am, "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?

  1. Use explain plan to check your cow-orkers truthiness.
  2. Insert the difference between the two tables, see the MINUS operator in the fine manual.

jg

--
@home.com is bogus.
It ain't the backup, it's the restore:
http://www.signonsandiego.com/news/computing/20070320-0509-lostdata.html
Received on Tue Mar 20 2007 - 15:17:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US