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

From: Luc Gyselinck <Luc.Gyselinck_at_nospampandora.be>
Date: Fri, 23 Jan 2004 08:00:43 GMT
Message-ID: <Lw4Qb.1215$kv4.192569@phobos.telenet-ops.be>

First, I would use an INSERT /*+ APPEND */ INTO ... This will generate much less REDO
Second, try to use an OUTER JOIN and force a HASH join:

INSERT INTO TABLE1(col1,col2,col3...col8) SELECT col1,col2,col3...col8
FROM TEMP_TABLE a,

     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(+)
AND b.col1 IS NULL

(I assume that col1 in table TABLE1 is a NOT NULL column)

To force a HASH join, use

INSERT INTO TABLE1(col1,col2,col3...col8) SELECT /*+ USE_HASH(a b) */ col1,col2,col3...col8 FROM TEMP_TABLE a,

     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(+)
AND b.col1 IS NULL   <-- this is the test for 'NOT EXISTS'

If you do not need an index on the TABLE1 table, do not create one. The above HASH join does not need an index.

Try changing the HASH_AREA_SIZE parameter (alter session set HASH_AREA_SIZE=<n>), unless you are using Oracle9i and the workarea_size_policy=AUTO. In the later case, the HASH_AREA_SIZE parameter is not used.

Hope this helps.

"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.
Received on Fri Jan 23 2004 - 02:00:43 CST