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@xxxxxxxxxxxx:

> 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

Other related posts: