RE: NOT NULL vs NULL column in a fact table
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mark.powell@xxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Sep 2007 17:31:02 -0400
I thought that the reason Gene wasn't doing that was to avoid wholesale row
migration. What you've suggested certainly will work, but it has the
potential to adversely affect performance. Whether that potentiality is
meaningful will vary case by case, but I was also envisioning in a
datawarehouse environment that Gene would have loaded the fact tables
densely.
Now he could add the nullable column and slightly modify his copy protocol
to create an image of each partition with the value populated and then
exchange the partitions back in as time allows. (Presuming other data in the
older partitions is changing slowly enough to get a given partition copied
and swapped back in between updating loads).
Then, as you have indicated, when all the rows in all the partitions are
eventually non-null for this column modifying it to be not null should be
possible (though the verification might run a while)!
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Powell, Mark D
Sent: Tuesday, September 04, 2007 4:16 PM
To: oracle-l
Subject: 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
