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

kokoiski_at_gawab.com (kokoiski) wrote in message news:<cada9ce1.0401222318.8a42937_at_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.

Here is what I did. First I created TEMP_TABLE and TABLE1 with NOLOGGING and no index on both tables and use the APPEND hint and it took less than 4 minutes to insert. I tried to index TABLE1 but the difference is only seconds 1 to 10 seconds faster.

I will take a look on the other suggestion later this week and I will let you know about the outcome.

Thank you for your help guys. Received on Sat Jan 24 2004 - 04:38:27 CST