Re: NOT NULL vs NULL column in a fact table

Yes, I am aware of the hakan factor issue ( I am on 9204 and hit it a few times before finally getting a solution)

thank you

Gene Gurevich

                                                                           
             tboss_at_bossconsult                                             
             ing.com                                                       
                                                                        To 
             09/04/2007 07:00          genegurevich_at_discover.com           
             PM                                                         cc 
                                       oracle-l_at_freelists.org (oracle-l)   
                                                                   Subject 
             Please respond to         Re: NOT NULL vs NULL column in a    
             tboss_at_bossconsult         fact table                          
                  ing.com                                                  
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

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