Re: Oracle SQL Condition Question...

Bhavesh,

There are a couple of things you can change to improve performance. The most glaring of these is that you have a 'NOT IN' clause in your SQL. This is probably the most notorious clause for causing bad performance. It will be faster to use 'NOT EXISTS' and test for a successful join where those two fields are equal in a subquery.

The other thing, and this is much more trivial, is your 'SELECT *'. When you execute an SQL statement with a wildcard (*), Oracle has to consult the data dictionary and find out all the column names. It's faster to explicitly type the column names in your SQL yourself, that way only a column name validation occurs, which tends to be faster.

Adrian Klingel
Metro Information Services

Bhavesh Gosar wrote:

> 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
Received on Wed Apr 22 1998 - 00:00:00 CEST