| Large Volume Updates And Performance [message #190178] |
Tue, 29 August 2006 10:03  |
Jackofalltrades
Messages: 3
Registered: July 2005
Location: London England
|
Junior Member |
|
|
Hi,
Ive got to do some large volume updates, the table has 24million rows, and of these I need to update one field for around 1 million rows. The table has 3 partions based on date.
The update statement is simple,
UPDATE X SET EXPIRY_DATE = SYSDATE
WHERE EXPIRY_DATE = '01-JAN-9999'
The expiry date column is indexed and its all been analysed,
the explain plan I have got back is
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
UPDATE STATEMENT 11
UPDATE X
PARTITION RANGE SINGLE INDEX RANGE SCAN REP_OWNER.CLGAF_LOCAL_IDX 8 K 64 K
The index CLGAF_LOCAL_IDX is the one on expiry date, the column Im updating.
Anyway I can make it run quicker, its been running now for a good couple of hours, and it showing as 40% complete. I dont really have an idea of how long it should take.
Any advice most appreciated.
|
|
|
|
|
|
|
|
|
|
| Re: Large Volume Updates And Performance [message #190263 is a reply to message #190205] |
Tue, 29 August 2006 23:02  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you are updating 1M of 24M rows, it will be heaps faster to rebuild the table:
CREATE TABLE new
... columns / partitions ...
PARALLEL
NOLOGGING
AS
SELECT ...
DECODE(expiry_date, to_date('99990101','YYYYMMDD'), sysdate, expiry_date),
...
FROM old
Then build the indexes and constraints, rename the old table, then the new table.
I guarantee you this will be heaps faster. For example, on my hardware (crappy 2-proc Linux box) it would take way less than an hour - probably about 25-30 mins plus indexes.
So why is this so much cooler than your way?
- No row migration across partitions. This affects both the table and locally partitioned indexes.
- No UNDO (rollbacks) on the table, this also affects the index on expiry_date.
- No(t much) REDO (archive logs).
ie. it is a MASSIVE reduction in I/O
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
