update x set y = null takes a long time

Question

At work, I have a large table (some 3 million rows, like 40-50 columns). I sometimes need to empty some of the columns and fill them with new data. What I did not expect is that

UPDATE table1 SET y = null

takes much more time than filling the column with data which is generated, for example, in the sql query from other columns of the same table or queried from other tables in a subquery. It does not matter if I go through all table rows at once (like in the update query above) or if I use a cursor to go through the table row by row (using the pk). It does not matter if I use the large table at work or if I create a small test table and fill it with some hundredthousands of test-rows. Setting the column to null always takes way longer (Throughout the tests, I encountered factors of 2 to 10) than updating the column with some dynamic data (which is different for each row).

Whats the reason for this? What does Oracle do when setting a column to null? Or - what's is my error in reasoning?

Thanks for your help!

P.S.: I am using oracle 11g2, and found these results using both plsql developer and oracle sql developer.

Answer 1

Is column Y indexed? It could be that setting the column to null means Oracle has to delete from the index, rather than just update it. If that's the case, you could drop and rebuild it after updating the data.

EDIT:

Is it just column Y that exhibits the issue, or is it independent of the column being updated? Can you post the table definition, including constraints?