Re: Large tables, updates, selects and varchars

chrism778_at_gmail.com wrote:
> 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.

1+ billion rows with or without partitioning?

Is this the last change that will ever take place or part of what may be ongoing modifications?

How is the new column going to be used? Will most queries need to access the new data? Why VARCHAR2(4000) and not CLOB?

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org