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

From: kokoiski <kokoiski_at_gawab.com>
Date: 22 Jan 2004 23:18:13 -0800
Message-ID: <cada9ce1.0401222318.8a42937@posting.google.com>

I am posting this again since my last post still does not appear after 30 minutes. I assume it is lost. Thanks.

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. Received on Fri Jan 23 2004 - 01:18:13 CST