kokoiski_at_gawab.com (kokoiski) 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.
It looks like col1-col5 form a primary/unique key for table1, in which
case you should declare the constraint and thereby index those
columns. Inserting 200,000 rows should not then take very long at
all.
Received on Fri Jan 23 2004 - 05:52:57 CST
