Re: char vs. varchar2
Friend : In my opinion, if you will use the ltrim(rtrim(column_char)) , i hope you solve the problem. Am i wrong ? Eriovaldo - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, April 22, 2002 7:12 PM > Is there any overhead (ie. internal conversion) in comparing a char to a > varchar2? We found an instance where a primary key in one table is defined > as char(2) and the foreign key referencing it from another table is > varchar2(2). We are going to change it, but I'm curious what, if anything, > happens when resolving the integrity? > > Lisa > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: YTTRI Lisa > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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.com -- Author: Eriovaldo Andrietta INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: char vs. varchar2
If the varchar2 column is less than 2 chars then it will be padded with blanks and THEN compared with CHAR column. That's all. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, April 22, 2002 6:13 PM To: Multiple recipients of list ORACLE-L Is there any overhead (ie. internal conversion) in comparing a char to a varchar2? We found an instance where a primary key in one table is defined as char(2) and the foreign key referencing it from another table is varchar2(2). We are going to change it, but I'm curious what, if anything, happens when resolving the integrity? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). ***1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: char vs. varchar2
I want to add that even if you update a field to a *smaller* size, initial data in the varchar2 column has to move to the empty space beyond the end of row data inside the block, leaving a hole in its original place, just as if it were updated to a longer string. Char data does not have this problem. This test is done with block dumps on 8.1.7 on NT. Yong Huang [EMAIL PROTECTED] Miller, Jay wrote: Actually char has some minor performance advantages over varchar2 when the column is frequently updated to a larger size since the full space is already claimed within the block. __ Do You Yahoo!? Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: char vs. varchar2
Actually char has some minor performance advantages over varchar2 when the column is frequently updated to a larger size since the full space is already claimed within the block. -Original Message- Sent: Wednesday, August 29, 2001 6:58 PM To: Multiple recipients of list ORACLE-L Isn't CHAR a declining feature, which is why Oracle is forcing the conversion to VARCHAR2 whenever possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: char vs. varchar2
Isn't CHAR a declining feature, which is why Oracle is forcing the conversion to VARCHAR2 whenever possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: char vs. varchar2
One thing you have to consider is the comparison semantic when using CHAR vs VARCHAR. You should consult the Oracle Doc on this. >>> [EMAIL PROTECTED] 08/29/01 03:35PM >>> I think that this subject comes up now and then, but I don't remember the conclusions We are creating a Data Warehouse and the subject of char vs varchar2 data types came up. I searched google and found a little bit on the subject (mainly there is no space savings between char(1) and varchar2(1)). Has anybody studied this? For example: If I know that all my data is 10 characters wide, should I not create the field as char(10)? Any reason to make it a varchar2(10)? Would sql scripts (select and dml) be faster with one type or the other? Any space savings with one type or the other (still given 10 characters of fixed data)? Thanks, Tom Tom Terrian Oracle DBA WPAFB - DAASC [EMAIL PROTECTED] 937-656-3844 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Terrian, Tom INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: char vs. varchar2
Thank you, I will check it out. Tom Terrian Oracle DBA WPAFB - DAASC [EMAIL PROTECTED] 937-656-3844 -Original Message- Sent: Wednesday, August 29, 2001 4:06 PM To: Multiple recipients of list ORACLE-L "Terrian, Tom" wrote: > > I think that this subject comes up now and then, but I don't remember the > conclusions > > We are creating a Data Warehouse and the subject of char vs varchar2 data types > came up. I searched google and found a little bit on the subject (mainly there > is no space savings between char(1) and varchar2(1)). > > Has anybody studied this? For example: If I know that all my data is 10 > characters wide, should I not create the field as char(10)? Any reason to make > it a varchar2(10)? Would sql scripts (select and dml) be faster with one type > or the other? Any space savings with one type or the other (still given 10 > characters of fixed data)? > > Thanks, > Tom > > Tom Terrian > Oracle DBA > WPAFB - DAASC > [EMAIL PROTECTED] > 937-656-3844 Tom, There is a somehow astonishingly little known - in spite of its having been around for at least the 15 last years - SQL function named VSIZE() which answers any question you may have about actual storage usage. For the really inquisitive, you have DUMP() as well. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Terrian, Tom INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: char vs. varchar2
"Terrian, Tom" wrote: > > I think that this subject comes up now and then, but I don't remember the > conclusions > > We are creating a Data Warehouse and the subject of char vs varchar2 data types > came up. I searched google and found a little bit on the subject (mainly there > is no space savings between char(1) and varchar2(1)). > > Has anybody studied this? For example: If I know that all my data is 10 > characters wide, should I not create the field as char(10)? Any reason to make > it a varchar2(10)? Would sql scripts (select and dml) be faster with one type > or the other? Any space savings with one type or the other (still given 10 > characters of fixed data)? > > Thanks, > Tom > > Tom Terrian > Oracle DBA > WPAFB - DAASC > [EMAIL PROTECTED] > 937-656-3844 Tom, There is a somehow astonishingly little known - in spite of its having been around for at least the 15 last years - SQL function named VSIZE() which answers any question you may have about actual storage usage. For the really inquisitive, you have DUMP() as well. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools & Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
