Home     |     Java    |     Php General    |     Oracle Database    |     Oracle Server  

MS Dynamics CRM 3.0

  •  Setting up and Configuring Microsoft Dynamics CRM 3.0
  •  Managing Security and Information Access
  •  Entity Customization: Concepts and Attributes
  •  Entity Customization: Forms and Views
  •  Entity Customization: Relationships, Custom Entities, and Site Map
  •  Reporting and Analysis
  •  Workflow
  •  Server-Side SDK
  •  Client-Side SDK
  •  Integration with External Applications

    • Cervo Technologies
    The Right Source to Outsource

    Sharepoint Portal Server KB

    Microsoft CRM Info

    WPF Interview Questions

    SilverLight Interview Qs

    Asp.Net 2.0 Interview Qs

    Asp.NET 1.1 FAQs

    Oracle Interview Questions

    SAP Interview Questions

    Oracle Server

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


    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

    "Joe" <joe1@tlen.pl> a crit dans le message de news: 1178764619.029339.113@w5g2000hsg.googlegroups.com...
    |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
    |

    Adding a column is just an update in dictionary. It is immediate.
    Filling the new column will take more time.
    There will mey be not fragmentation but row migration after that
    and a table reorganization may be useful (depending on how many
    rows will migrate).

    Regards
    Michel

    -----------------------------------------------Reply-----------------------------------------------

    On May 10, 12:39 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

    > Adding a column is just an update in dictionary. It is immediate.
    > Filling the new column will take more time.
    > There will mey be not fragmentation but row migration after that
    > and a table reorganization may be useful (depending on how many
    > rows will migrate).

    > Regards
    > Michel

    well.. i takes not even a second to add a new column WITH a default
    value of 0 (through Toad) on 19M records table.
    Can you tell me some more about table reorganization? Do I have to
    worry about it or just leave it up to Oracle magic? :)

    -----------------------------------------------Reply-----------------------------------------------
    On May 10, 9:46 am, Joe <joe1@tlen.pl> wrote:

    > On May 10, 12:39 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

    > > Adding a column is just an update in dictionary. It is immediate.
    > > Filling the new column will take more time.
    > > There will mey be not fragmentation but row migration after that
    > > and a table reorganization may be useful (depending on how many
    > > rows will migrate).

    > > Regards
    > > Michel

    > well.. i takes not even a second to add a new column WITH a default
    > value of 0 (through Toad) on 19M records table.
    > Can you tell me some more about table reorganization? Do I have to
    > worry about it or just leave it up to Oracle magic? :)

    Because most of your columns are varchar2 - variable length - most
    probably you had enough free space for "0" values. Once the zeros
    start changing to larger values you may have to reorg the table.
    Anyway, you should check the current state and keep an eye on it until
    you can do a reorg (online or offline).

    -----------------------------------------------Reply-----------------------------------------------
    On May 9, 7:36 pm, Joe <joe1@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-spac...

    Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc