Re: adding a new column to 19 million records table - how does it really works on 10g?

On May 9, 7:36 pm, Joe <joe1..._at_tlen.pl> wrote:
> I have a pretty big table with 10 columns, most varchars2 (50).
> adding a new column to table that already have 19M records takes half
> a second (through Toad).
> question: how does oracle organize data inside table space, and does
> on should make any database defragmentation after adding additional
> column to allready so much filled-out table?
>
> thanks for help

Please read the concepts manual, row format and size is explained there, as well as tablespace organization. Also, row chaining and migration. You need to worry about the latter when you update rows and they no longer fit in the block where they've been, as Michel and Valentin have alluded to. There are scripts in your $ORACLE_HOME/ rdbms/admin directory called something like utlch*sql to determine if this is a problem - search the docs for how to use those. After you learn the concepts. There are also informative columns in dba_tables and some other views. Be sure you understand PCTFREE and PCTUSED.

Fragmentation has a particular set of definitions with respect to Oracle, and is generally not a problem. Certain things can happen though, especially with mass deletes.

jg

--
@home.com is bogus.  http://www.10zenmonkeys.com/2007/05/09/keith-henson-back-in-jail-space-elevator-will-have-to-wait/