How to update Large Number of Rows?

[wellster34]

Hi,

I have a table that contains 19 million rows. I need to update 14 million of them to change a value from NULL to 0. Is there a quick way to do this?

TABLE
PK COLUMN 1
PK COLUMN 2
PK COLUMN 3
PK COLUMN 4
PK COLUMN 5
COLUMN A
COLUMN B
COLUMN C
COLUMN D (YEAR)
COLUMN E (MONTH)

I need to update the COLUMN A in this example to 0 where it is NULL. As you can see, it is not part of the Primary Key. So, should I build an index on the column? Run it by using the Year and Month columns?

Any ideas are greatly appreciated!!!

Thanks,

 

[SantaMufasa]

Wellster,

You are updating 75% of the rows in your table. Oracle recommends not using an index if you are accessing more than 6%-10% of your table. Therefore, you do not want to use and index...it will slow you down in this case...the fastest method of doing what you want is:

UPDATE <table_name> SET column_a = 0 WHERE column_a IS NULL;
COMMIT;

Let us know how this worked for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:28 (17Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:28 (17Nov04) Mountain Time

 

[LKBrwnDBA]

A more faster way to update is:

CREATE TABLE NEW_TABLE NOLOGGING
AS SELECT ... FROM OLD_TABLE;

And then create the indexes.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb