Re: Insert statement taking a very long time to complete

Replacing the uncorrelated subquery with a correlated one as suggested by Patricio should improve the join between tablea and tableb by encouraging Oracle to use an index for the join - provided one exists on the join column.

You should check the query plan to make sure this is happening.

If the necessary index is there but Oracle still doesn't use it you may need to run ANALYZE (or its built-in package equivalent).

Andy

On Mar 21, 4:30 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
> On Mar 21, 1:17 am, "joel garry" <joel-ga..._at_home.com> wrote:
>
>
>
>
>
> > 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.h...Hide quoted text -
>
> > - Show quoted text -
>
> I don't have much experience, but when I faced this problem, it was an
> issue with index on base table,
> delete rows were more than 50%, it might be cause then rebuild
> indexes.For this you have to analyze index with validate structure and
> then check index_stats.
> It might be a problem with sub table query.
> If it is possible for you to provide explain that would really help
> everyone to see what actually is happening with query.- Hide quoted text -
>
> - Show quoted text -
Received on Wed Mar 21 2007 - 05:28:52 CDT