RE: NOT NULL vs NULL column in a fact table

Alter the table adding nullable column
Populate column
alter the table modifying column to be not null Update statistics

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Tuesday, September 04, 2007 2:59 PM To: oracle-l
Subject: NOT NULL vs NULL column in a fact table

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