| Update a new column in a Large Table [message #173479] |
Mon, 22 May 2006 12:07  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
hello
This is the SQL statement and Explain plan
it was running for more than 7 minutes got hanged and i have to stop, Basically we added this proc_dt as a new column in table having more than 30 million rows now have to update this column based on value from other table with a join.
In this case what should be done,
i was thinking of doing 1 million rows at a time
or another option is like create table as select and do the update here..
not sure..
Can i rewrite SQL in a better way,
There is no index on Proc_dt column.
Stats are updated daily.
Version Oracle 9i Release2.
UPDATE img
SET img.proc_dt = (SELECT pymt.proc_dt
FROM pymt
WHERE pymt.img_rear_id = img.img_id)
WHERE EXISTS (SELECT 'x'
FROM pymt
WHERE pymt.img_rear_id = img.img_id);
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
UPDATE STATEMENT Optimizer Mode=CHOOSE 11 M 38375
UPDATE R1APP.IMG
HASH JOIN SEMI 11 M 144 M 38375
TABLE ACCESS FULL R1APP.IMG 37 M 251 M 17960
INDEX FAST FULL SCAN R1APP.XIE_PYM_IMGRERID 11 M 66 M 2646
TABLE ACCESS BY INDEX ROWID R1APP.PYMT 1 7 4
INDEX RANGE SCAN R1APP.XIE_PYM_IMGRERID 1 3
Thanks.
[Updated on: Mon, 22 May 2006 12:08] Report message to a moderator |
|
|
|
| Re: Update a new column in a Large Table [message #173659 is a reply to message #173479] |
Tue, 23 May 2006 17:37  |
agostino_neto
Messages: 180
Registered: July 2005
|
Senior Member |
|
|
Hi,
Perhaps you can try one of the following:
- Parallel Execution
- Have an idea of COUNT(distinct img.img_id)
FROM img, pymt
WHERE pymt.img_rear_id = img.img_id
If the result is small (store the results in my_img) then you can change
WHERE EXISTS (SELECT 'x'
FROM pymt
WHERE pymt.img_rear_id = img.img_id);
by
WHERE img.img_id in (SELECT img_id
FROM my_img);
Make sure your rbs are large enough.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
