Re: NOT NULL vs NULL column in a fact table
- From: "Ian Cary" <Ian.Cary@xxxxxxxxxxxxxx>
- To: genegurevich@xxxxxxxxxxxx
- Date: Wed, 5 Sep 2007 09:33:26 +0100
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@disco|
| | ver.com |
| | Sent by: |
| | oracle-l-bounce@fr|
| | eelists.org |
| | |
| | |
| | 04/09/2007 19:58 |
| | Please respond to |
| | genegurevich |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: oracle-l@xxxxxxxxxxxxx
|
| 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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
