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

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0401230352.66a7caad_at_posting.google.com>...
> 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.

Col1-col5 are not unique and some are varchar2 so I am not sure if indexing this columns will help a lot.

Thanks. Received on Sat Jan 24 2004 - 04:43:00 CST