Re: what is fastest way to add 4 columns to a 100 million row table

On 13 May 2001 09:42:08 -0500, sdfes_at_dsf.com (sdfsd) wrote:

>
>Which goes back to orginal question. Given a 100 million row table we want to
>add 4 columns they will have no data intially but they might have it later.
>two quesitons:
>
>what is the 'FASTEST' way to do it. i think you said an alter table would do
>it
>
>Is that the BEST way. is an alter table going to increase risk of row
>migartion if we update those columns later?
>
>>

OK, I thought Jonathan gave you all the info you needed. I'll try and make it more clear:

You can always add more columns to a table. Takes a few seconds and has nothing to do with size of table. But if you intend to populate those columns for EVERY row you ALREADY have in the table, you may have a problem with migrated rows. Because additional data into existing rows may push them off their current block.

This migration thing may happen to existing rows, but won't affect new rows added after new columns have been defined. Unless you only populate these extra columns AFTER you have inserted the new rows. In which case you're back to square one re migration!

PCTFREE is the amount of free space in each block to cater for row expansion without causing row migration. Think of it as an insurance policy against row migration. You can find out what it currently is by looking in view DBA_TABLES or USER_TABLES, it's one of the columns there.

Now, you stated that you wanted to make these new columns also NOT NULL. As Jonathan advised, you can do that by adding a not null constraint and making it NOVALIDATE (check the syntax for adding constraints). This means the constraint will be active for new rows but won't check existing rows. That lets you implement the not null without having to first populate new columns in existing rows.

But of course, you can go back later and change those previous rows. In which case the points above about row migration will apply. And you may have to do it in chunks rather than the whole table in one go or else you'll blow your rollback segments. That's when you'll need a suitable WHERE clause in your UPDATE statement.

At that sort of size, you'de be much better off with partitioned tables. But as you said, it has a cost. You gotta weigh that cost against the increased hoops you gotta go through when not partitioning big tables.

Talk to your DBAs, they will be able to advise you as to details on all this.

Bottom line is:

Takes a minute or so to add the new columns, with NOT NULL constraint and NOVALIDATE. And you can add new rows straight away.

Takes quite a long time and resources to make sure ALL rows have those columns populated. And it may give you a problem with row migration for existing rows.

And your DBAs will hate you if they have to rebuild that size of table! :-)

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Sun May 13 2001 - 12:00:14 CDT