| Performance Issue [message #370148] |
Fri, 22 December 2000 04:06  |
Graham Leach
Messages: 2
Registered: October 2000
|
Junior Member |
|
|
I have a large table table1 and I want to do an update like
UPDATE table1
SET field1 = 'AA',
field2 = 'BB'
WHERE field1 = 'XX'
AND field2 = 'YY';
The number of rows to be updated is about 1 million and the table has about 15 million rows altogether.
I want to do my update in a cursor
CURSOR c1 is
SELECT rowid
FROM table1
WHERE field1 = 'XX'
AND field2 = 'YY';
then in the cursor loop
UPDATE table1
SET field1 = 'AA',
field2 = 'BB'
WHERE rowid = cursor.rowid;
Should I index field1 and field2 ?
Is there a more efficient way of doing this?
Thanks
|
|
|
|
| Re: Performance Issue [message #370149 is a reply to message #370148] |
Fri, 22 December 2000 04:41  |
Sandeep Deshmukh
Messages: 13
Registered: October 2000
|
Junior Member |
|
|
Graham,
Considering the hugh number of rows under updation,I will suggest not to go for cursor on the same table.If you try to commit after particular number of rows,you might be hit with "cursor across commit" problem (eg. snapshot too old).
Instead,I will suggest use index on the columns in "where" clause.Use a big enough rollback segment ( avg_row_length * no. of records should fit in).Before starting updation ,set rollback segment as -
dbms_transaction.use_rollback_segment('RBS_X')
And you can give the update query with the where clause.
Hope it works.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
