Re: NOT NULL vs NULL column in a fact table

In this situation you could add the column allowing nulls AND add also add triggers to reject any insert / update that leaves the new column null.

When a table gets to the state where every row has a value for the column, make the column NOT NULL and drop the triggers.

What do the more experienced members of the list think of this option?

Brett Hammerlindl

Quoting genegurevich_at_discover.com:

> Hi all
>
> I have several medium to large fact tables to which I need to add a NOT
> NULL column. In the past I would do the following:
>
> - copy the data from the fact table to a backup table
> - truncate the fact table
> - add the new column to the fact table as NOT NULL
> - copy the data back from the backup table to the fact table with some
> predetermined default value (say, -9)
> for the new column
> - rebuild indices
> - reanalyze the fact table.
>
> This time however the number of the tables and the sizes are too big and I
> don't think my normal process
> will complete within a reasonable time. So I am now considering just adding
> this column as nullable, and
> modify it as not null when older partitions are dropped (in a year or two
> or so) and I will ask the reporting team
> to run the reports for this new column only for the months starting with
> the one when this column was added.
>
> What kind of problems am I asking for?
> thank you
>
> Gene Gurevich
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l