Help with speeding up an update sql statement

[robo100]

Good morning

I am trying to do an update via a sql statement on a table containing 47 million rows from a table containing 250 rows.

The structures are as follows

Table 1 - 47 million rows
Offer_id number(5)
Branch number(5)
item vachar2(5)
Date date
sales_value number(9,2)
sales_units number(7)
days number(2)
avg_units number(9,2)
avg_value number(9,2)

I have a primary key on the table of offer_id, branch, item and date.

Table 2 - 250 rows
Offer_id number(5)
days number(2)

I have a primary key on the table of offer_id

I am trying to update Table 1's days with the days from table 2 where the offer_id's match. I have tried using the following sql statement but it is very slow and I am being moaned at from the dba regarding the tablespace used in the undo.

update table_1 a
set days =
(select days from table_2 b
where b.offer_id = a.offer_id)
where exists
(select offer_id from table_2 b
where b.offer_id = a.offer_id);

Any help on how I could speed this up would be greatfully received.

Thanks

 

[taupirho]

Do you have stats on your tables. If not put them on.

 

[robo100]

Sorry I'm not a dba. What do you mean by stats, statistics have been computed for these tables I believe. Is this what you mean.

 

[taupirho]

Another option is to use something like the following:

1) use a CTAS to create a new table (say t3) to get the data you want from t1 (big table) and t2 (little table)
2) drop the orig big table t1
3) rename t3 to t1
4) re-build indexes on your new t1 table

Will this be faster? I don't know - you'll have to try it and see.

 

[hoinz]

Hello,

regarding the problem of being moaned at from your the dba:
Speeding up your query will not necessarily reduce the amount of undo tablespace needed.
You might want to split up your task in a loop, only updating 1000 or 10000 lines at a time, and committing then. (But be aware that this will make the whole run even slower!)

regards

 

[Dagon]

An alternative form, if you have primary keys on both tables, is to use:

update (select a.days as a_days, b.days as b_days from table_1 a, table_2 b
where b.offer_id = a.offer_id)
set a_days = b_days

This may be faster.