Re: NOT NULL vs NULL column in a fact table
- From: Brett Hammerlindl <hammerl@xxxxxxxxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx
- Date: Tue, 4 Sep 2007 18:53:22 -0600
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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
