Re: NOT NULL vs NULL column in a fact table

Not sure what version you're on, but I ran into a debilitating bug related to exactly this situation in 9i. I needed to add a column, added it as suggested by others (alter table add column, populate, alter column not null). If you load data via alter table exchange partition and have local bitmap indexes (like I'd assume you do in a DW environment), you can corrupt something called the "Hakan Factor" within oracle's data dictionary.

See Metalink note 248634.1 and bugid 4221789 for the orig documents. It says its fixed in 9208 and in the base of 10g, but we've all seen issues persist past major releases.

The thing i'd be worried about in your case is the introduction of random data dictionary corruption like the above by adding and dropping columns from tables. In the above case, there was some obscure event I could have set before exchanging the partitions that would have prevented the issue.

Based on what you say though, you may not have a choice. But if at all possible I'd do a ctas to create a clean table.

my 2 cents, todd

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

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