RE: RE: Create Table..As Select: Number formats
I'm perhaps a little late replying, but have you looked at using alter table xxx modify (column number(1,2)); for example to modify the number format after creating the partition. I'm expecting there to be limits but it sounds like you are only trying to increase the number type size not decrease it. Also, you might wear some table scans while it inspects data depending on the change but might be acceptable depending on volumes and timeframes. [EMAIL PROTECTED] isys.co.uk To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: RE: Create Table..As Select: Number formats .com 02/10/2003 03:09 Please respond to ORACLE-L Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise
RE: RE: Create Table..As Select: Number formats
:31 I'm trying to create a table using 'Create Table...As Select...' ... I don't think that there is any problem here. Specifying the number of digits is largely cosmetic - consider it asa default mask. It doesn't affect how data is stored inside the tables AFAIK. Regards, Stephane Faroult Oriole The problem is when I try to exchange the newly created table into the partitioned table - the designers (in their infinite wisdom) have specified number formats for that table. alter table daily_total exchange partition jun_02 with table dt_temp including indexes gives me the error: * ERROR at line 1: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION Unless I get the column types to match exectly - I can't ask for a change in table design to remove the awkward formatting until I've at least tried to get the format to work in the 'Create Table...As Select..' Cheers Simon Anderson Simon, Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Create Table..As Select: Number formats
Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Create Table..As Select: Number formats
Thanks to everyone who responded to this thread - Option A is now to persuade the designers to remove the Number formatting from the parttioned table, Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */ Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue. No, option B is as fast as CTAS (as long as you don't have any indexes on the table). Just make sure that your append hint works... Tanel. Cheers Simon Anderson I'm trying to create a table using 'Create Table...As Select...' ... Are you sure that it comes from the NUMBER() columns? Reminds me of the problem when you have a NULL in a UNION, which must be explicitly cast with a to_number(), to_date() or to_char(). Might it come from some NULL in your CREATE TABLE AS SELECT ... ? Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Create Table..As Select: Number formats
No, option B is as fast as CTAS (as long as you don't have any indexes on the table). Just make sure that your append hint works... Also you have to specify NOLOGGING on table or tablespace level when doing insert /*+ APPEND */ or use NOLOGGING hint if you're on 9i. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
