Large tables, updates, selects and varchars
Date: Wed, 2 Apr 2008 15:07:00 -0700 (PDT)
I am using Oracle 10g (10.2.0.3.0) and have a large table (1+ billion rows). It currently has about 15 columns in it. I have a requirement for a new varchar2(4000) column to go with the current data in that table. I need to update this column after it has been added to the table. I have been told that I may be better off putting the column in a separate table because by adding it to an existing table, Oracle has to jump around the hard drive to update it and find it, and therefore this will degrade performance. Of course, by putting it in a separate table, a join will be required on selects.
From a purely design point of view, it makes a lot more sense to add the column to the existing table, but I don't know the internals of Oracle. When modifying existing tables on large Oracle databases, do you generally have to be wary of what columns you add?
I can't really test this ahead of time--the update will take a very long time to perform, because there are many rows and a calculation is involved.
Thanks. Received on Wed Apr 02 2008 - 17:07:00 CDT