Home » RDBMS Server » Performance Tuning » Large Volume Updates And Performance
Large Volume Updates And Performance [message #190178] Tue, 29 August 2006 10:03 Go to next message
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 #190185 is a reply to message #190178] Tue, 29 August 2006 10:29 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
UPDATE X SET EXPIRY_DATE = SYSDATE WHERE EXPIRY_DATE = TO_DATE('01-JAN-9999','DD-MON-YYYY').
Free clue - '01-JAN-9999' is a STRING not a date.
In Oracle strings are enclosed in single quote marks.
Re: Large Volume Updates And Performance [message #190203 is a reply to message #190185] Tue, 29 August 2006 12:24 Go to previous messageGo to next message
Jackofalltrades
Messages: 3
Registered: July 2005
Location: London England
Junior Member
Cheers corrected it to that but it still runs like a dog Mad
Re: Large Volume Updates And Performance [message #190205 is a reply to message #190203] Tue, 29 August 2006 12:53 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
Is the table partitioned on expiry_date? If so then it likely has to migrate the rows across partitions.

Try posting the full explain plan contained in a [CODE][/CODE] block.
Re: Large Volume Updates And Performance [message #190263 is a reply to message #190205] Tue, 29 August 2006 23:02 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Connection Latency
Next Topic: Temp Data files Size
Goto Forum:
  


Current Time: Sat Dec 03 09:55:00 CST 2016

Total time taken to generate the page: 0.10092 seconds