Forum Stats

  • 3,757,045 Users
  • 2,251,189 Discussions
  • 7,869,703 Comments

Discussions

Adding column to existing large partitioned table

user5716448
user5716448 Member Posts: 1,737 Silver Badge
edited Nov 3, 2020 10:05PM in SQL & PL/SQL

Hi,

We are looking to add column (new measure) to some large tables (over a a billion rows)

This is a partitioned table - guess can still use alter table and won't invalidate indexes etc and the alter table command will execute quickly?

Is this a correct assumption?

Also with regards space notice the columns on table defined as NUMBER with no actual size.

Is it correct in ORACLE

Values stored in a NUMBER column take up only as much space as needed?

Thanks

Best Answer

Answers

  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge

    Thanks for reply.


    Believe they will be auto-recompiled when next run (version 11.2.03) - is this the case?

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,036 Silver Trophy

    Yes, Oracle will attempt to recompile the code before it is used. That said, you can use DBMS_UTILITY.COMPILE_SCHEMA to recompile code right after the table change is made.

    user5716448
  • James Su
    James Su Member Posts: 1,114 Gold Trophy

    if the new column has a default value and you do not have "not null" constraint then it will have to populate the existing rows.

  • user5716448
    user5716448 Member Posts: 1,737 Silver Badge

    Thanks for updates.


    column optional and no default value thus from this respect should be o.k. but useful info.