Very Computer

I am working on a project for Oracle v8.0.3 on Windows NT.  One of
functions has to update a very large table witch has ten million rows
every half hour with about ten thousand rows which are stored in a
temporary table.
I tried update it row by row. The performance is very slow.
I also tried to use a SQL statement to update it. It always uses full
table scan on the big table instead of using index even though I
created primary key on it. The SQL statement looks like:

UPDATE bigtable BT
SET ( col1, col2, col3 )
=
(
SELECT col1, col2, col3
FROM  smalltmp tmp
WHERE BT.key1 = TMP.key1
  AND BT.key2 = TMP.key2
)

It took very very long time to be done because it used two full table
scan even they have indexes on the keys.

Can anybody help me to figure out how to avoid full table scan on the
big table and speed up the performance?

Thanks

Jianrong  M

Then you should be able to get rid of the full table
scans.

The || may not be the best approach. I haven't done
this stuff in a while.  Play with it and I'm
sure you can speed things up.

You might want to try PL/SQL - use a cursor on the
smalltmp table and for each row update the big table.
That would likely give you a reasonable result.

Good luck,
Terry

WHERE (BT.key1,BT.key2) in ( SELECT TMP.key1, TMP.key2
                             FROM smalltmp)

Would be safer and immensely faster
(functions inhibit index usage)
--
------------
Kind reGards
     \ /   |
      X    |
     / \   x
     Gerard

Quote:> Hello,
> Generally speaking, a select or update statement
> taht does not have a where clause implies that the
> whole table is the target of the query or update.
> Hence the full table scans.
> Your update statement above does not have a where clause.
> If you include one, say,
> UPDATE bigtable BT
>  SET ( col1, col2, col3 ) =
>                           ( SELECT col1, col2, col3
>                             FROM  smalltmp tmp
>                             WHERE BT.key1 = TMP.key1
>                             AND BT.key2 = TMP.key2
>  )
> WHERE BT.key1||BT.key2 in ( SELECT TMP.key1||TMP.key2
>                             FROM smalltmp)

...>
> Good luck,
> Terry

-- begin
declare
 cursor c_smalltmp is
   select key1, key2, col1, col2, col3
    from smalltmp;
begin
 for r_smalltmp in c_smalltmp loop
   update bigtable set
     col1 = r_smalltmp.col1,
     col2 = r_smalltmp.col2,
     col3 = r_smalltmp.col3
    where key1 = r_smalltmp.key1 and key2 = r_smalltmp.key2;
 end loop;
end;
/
-- end

Happl

Or better still

   where exists
       (select 'x'
        from small TMP
        where TMP.key1 = BT.key2
        and TMP.key2  = BT.key2
        and TMP.key3  = BT.key3)

(This works much better if small.KEY1=BT.key1
                           small.KEY2=BT.key2
                           small.KEY3=BT.key3 )

and you have an index for small (KEY1,KEY2,KEY3)
The second query needs not to access the table since
the query can find all the info from the index itself )

CIAO

Joseph Sumalbag

In a completely differnt direction;

Why not use Export, truncate the temp table, and then Import ?  For better
 Export and Import performance, compile the EXP/IMP executables in single-task
 mode and use a direct Oracle connection ( No MTS stuff).   This solution would
 probably be better for a non-OLTP type of application due to the extensive IO
 for the index updates during the import.

Cliff