Home » RDBMS Server » Performance Tuning » Effective way to update a column to null (based on a condition) quickly in a huge 800 GB table (Oracle 11.2.0.4 on Linux)
Effective way to update a column to null (based on a condition) quickly in a huge 800 GB table [message #629225] Thu, 04 December 2014 12:21 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Hi,



I have a huge 800 GB table and need to quickly set a CLOB column of that table - which is taking up all that space to null. Now there is a criteria for this update - only based on a condition I have to set it null. Like if the length of that column is more than x bytes then set it to null. The question is that : if I do a simple UPDATE, for that CLOB column and which will affect about 600 GB, can that space be effectively reused for new inserts updates in that same table? I am guessing yes - because since setting that column to null means space is available in that table so it should be able to be reused , but I would like to know about the opinion on it.



Thanks,

OrauserN
Re: Effective way to update a column to null (based on a condition) quickly in a huge 800 GB table [message #629226 is a reply to message #629225] Thu, 04 December 2014 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Space for CLOB can be reused only for the same CLOB (any row) not for the other columns of the table.

[Updated on: Thu, 04 December 2014 13:07]

Report message to a moderator

Re: Effective way to update a column to null (based on a condition) quickly in a huge 800 GB table [message #629227 is a reply to message #629226] Thu, 04 December 2014 13:05 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thanks a lot Mike!!
Previous Topic: INTERNAL_FUNCTION appears at explain plan
Next Topic: Has anyone ever seen BEGIN_OUTLINE_DATA alone change a plan
Goto Forum:
  


Current Time: Tue Jan 16 20:17:11 CST 2018

Total time taken to generate the page: 0.02716 seconds