Re: Slow insert

From: <mgogala_at_rocketmail.com>
Date: Mon, 14 Dec 1998 01:41:57 GMT
Message-ID: <751qd5$qta$1@nnrp1.dejanews.com>

In article <3670C29C.48BA038D_at_dplus.net>, Em Pradhan <empradhan_at_dplus.net> wrote:
> Sorry for previous one...
> I think I slipped....in prev one..
> later
>
> Lito Dizon wrote:
>
> > Why is this so slow? Slow meaning 10 hours. Ans the
> > further it gets, the slower it gets.
> >
> > insert into TABLEA
> > select * from TABLEB
> > where KEY not in (select KEY from TABLEA )
> >
> > Both tables are indexed on KEY. TABLEA has 9000 rows
> > while TABLE B has 12000 rows.
>
>

The problem here is that you're doing a cartesian product with approximately 120,000,000 resulting from it. That's not politically correct way of doing things.

My suggestion is:

  insert into TABLEA
   select * from tableb
    where key in (

      select /*+ index_asc(tablea keyaindex) */ key from tableb
        minus
      select /*+ index_asc(tableb keybindex) */ key from tablea

)

This will utilize fast full index scans to get keys from tables A and B, use sort/merge to extract the differences and execute the insert. Index_asc hint is used speed up both scans and sort/merge. I am sure it will take much less then 10 hours. Regards,

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sun Dec 13 1998 - 19:41:57 CST