Re: Oracle SQL Condition Question...

Besides the possible slow subquery, you may have other things to consider. An INSERT like this (around 68,000) rows
1. The transaction use a large rollback segment. Dedicate a large rollback segment for this transaction.
2. Tab3 may be acquring multiple extents to hold the newly inserted rows. You may want to manually allocate a large extent to TAB3 before you run this query.
3. You may want to disable the indexex on TAB3 and after the INSERT is done, you may recreate the indexes
4. You may also try breaking this query into multiple steps, with intermittent COMMITs (with a range of primary key values in the SELECT)

Good luck,
CM

In article <6hhahh$ia2$1_at_nnrp1.dejanews.com>,   smoore_at_accordhr.com wrote:
>
> In article <6hglsp$s0d$0_at_207.17.114.24>, "Todd Marshall"
> <laz777_at_semaphore.com> wrote:> > Your problem is the 'not in' part of the
> query. It's always much faster to> find out if something 'is' in a
 table
> rather than not in a table. Without> some background on your application, I
> can't provide you with a reasonable> alternative though....> > Bhavesh
> Gosar wrote in message <6hg4p8$se0$1_at_engnews2.Eng.Sun.COM>...> >Hi,> >> >I
> have 2 tables> >> >tab1 tab2> >==== ====> >.. ..> >.. ..> >f1 f2>
> >.. ..> >.. ..> >> >Both the tables above contain approx. 60,000 Records
> > >and have indexes on f1 and f2.> >> >I have an SQL which is the foll..>
> >> >Insert into tab3 ( select * from tab1 where tab1.f1 not in> > (select
> distinct tab2.f2 from tab2));> >> >The problem is this SQL takes around
> 40-45 hours to execute...> >> >Am i missing something here...Or any
> Workarounds Possible...??> >> >TIA,> >Bg> >> Seems like I remember
> seeing a tip somewhere that doing aNOT EXISTS is quicker than a NOT IN. (I
> couldn't find thematerial to verify.) Try rewriting the insertsubquery as
> follows:select * from tab1 where not exists (select 'x' from tab2
> where tab1.f1 = tab2.f2)
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 23 1998 - 00:00:00 CEST