Re: Insert statement taking a very long time to complete

On Mar 20, 4:08 pm, "gor..._at_panix.com" <gor..._at_panix.com> wrote:
> On Mar 20, 2:24 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:
>
> > Depending on your version, you could use
> > a merge statement.
> > Here's an example:http://psoug.org/reference/merge.html
>
> Unfortunately, I can't use merge. I'm using Oracle 9i, which requires
> action be taken both when the merge condition is matched and when it
> isn't, which isn't suitable for my particular application.
>
> Thanks for the reply though. :-)
>
> John Gordon
> gor..._at_panix.com

Your original statement:

  insert into tableb(pk, f1, f2, f3)
  (select * from tablea where tablea.pk not in (select pk from tableb))

chenge the subquery to an outer join like this:   (select tablea.* from tablea , tableb
    where tablea.pk=tableb.pk(+)

        and tableb.pk is null )

Note the IS NULL in the where clause!

   Ed Received on Wed Mar 21 2007 - 13:46:37 CDT