Re: Oracle expert please help. Slow insert in Oracle 8i and 9i. Urgent!

The first thing you need to do is find out the execution path for the select statement.

Since Oracle does not unnest NOT EXISTS
subqueries, you are probably going to see a FILTER as the TEMP_TABLE is scanned
and the TABLE1 is probed for each row
(in effect a short-circuit nested loop).

If this is the case, and looking at your figures, you may be able to optimise this by putting the an explicit UNNEST hint to turn the FILTER into a hash anti-join. However, you may then need to optimize the hash anti-join by increasing your hash_area_size to allow the relevant set of columns from TABLE1 to be hashed into memory.

(The UNNEST hint goes after the SELECT in the subquery).

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG -  v$ and x$
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"kokoiski" <kokoiski_at_gawab.com> wrote in message
news:cada9ce1.0401222244.4be2fe1a_at_posting.google.com...


> Hi,

>

> I have two tables below.

>

> TEMP_TABLE - No index with 200,000 rows

> TABLE1 - No index with 60,000 rows

>

> INSERT INTO TABLE1(col1,col2,col3...col8)

> SELECT col1,col2,col3...col8 FROM TEMP_TABLE a

> WHERE NOT EXISTS(SELECT 1 FROM TABLE1 b

>                  WHERE a.col1 = b.col1

>                  AND a.col2 = b.col2

>                  AND a.col3 = b.col3

>                  AND a.col4 = b.col4

>                  AND a.col5 = b.col5)

>

> I tried to index TABLE1 using col1 to col5 but still it is slow while

> using the index on TABLE1 and full table scan on TEMP_TABLE.

>

> The statement above is the result of breaking down the original

> statement that hungs. The original statement insert directly to TABLE1

> using NOT IN TABLE1 from 2 different table and a view but I narrowed

> it down to the INSERT to TABLE1 above.

>

> I tried to alter the tables to nologging and use the APPEND hint but

> still slow.

>

>

> Thank you in advance.