Re: Reorganizing the DB.. the tricky way

Anton Buijs wrote:

> Rick Denoire <100.17706_at_germanynet.de> schreef in berichtnieuws
> e3319vg58ngj27fk8t4uaji6l94bh5av72_at_4ax.com...
> | "Anton Buijs" <aammbuijs_at_xs4all.nl> wrote:
> | One of the most popular "hobbies" of the developers is to add new
> | column to existing tables and to populate these using update. Don't
> | you think that - assuming the blocks have no more space free for the
> | longer rows - this would cause ALL blocks to become migrated? In
> | consequence, every input request for one record would cause 2 read
> | operations, so making things 100% slower...
> |
> | Don't know how to avoid this. One can't just set PCTFREE to 40% for
> | every table, just in case one developer decides to add one column with
> | large fields.
> |
> | Bye
> | Rick Denoire
>
> Increasing pctfree would cause waste of space. You can't avoid many migrated
> rows when columns are added, I agree. So once in a while you need to reorg
> such tables, not very frequently, but it can be necessary now or then.
> There is a performance penalty but I won't say it gets 100% slower. Maybe
> the 2nd block was necessary too, to read the other rows matching the query,
> it could be read anyway by the OS or because of the Oracle settings
> (multiblock reads), required by another query. The total picture is always
> much more complex than looking at 1 session.

I would argue that in a production database there is no place for ALTER TABLE ADD column as is being assumed here. That to do so is irresponsible. The existing data should be dumped to a temp table, the table truncated, then modified, and the data returned to the table in combination with whatever values are required by the modification.

Daniel Morgan Received on Sun Apr 06 2003 - 16:36:16 CDT