Re: NOT NULL vs NULL column in a fact table

I think something similar to your usual approach is probably still the best. I would do the follwing.

• create table original_new ... (same as original but with new column added) nologging;
• insert /*+ append */ into original_new select o.*,-9 from original o;

(You may want to parallelise this step)

• build indexes against the new table the same as the old (nologging)
• alter new table and indexes back to log mode
• gather new table and index statistics
• replicate permissions on the original table to the new (and anything else e.g. triggers, constraints)
• rename original to original_old
• rename original_new to original

This should be the most efficient way of performing the task and also gives you a fallback (just rename the tables back) should anything go wrong.

Cheers,

Ian

|---------+----------------------------->
|         |           genegurevich_at_disco|
|         |           ver.com           |
|         |           Sent by:          |
|         |           oracle-l-bounce_at_fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           04/09/2007 19:58  |
|         |           Please respond to |
|         |           genegurevich      |
|         |                             |
|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       oracle-l_at_freelists.org                                                                       |
  |       cc:                                                                                                    |
  |       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



This email was received from the INTERNET and scanned by the Government
Secure Intranet Anti-Virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) In
case of problems, please call your organisation's IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.






For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet Anti-Virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
--
http://www.freelists.org/webpage/oracle-l