| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: char vs. varchar in a data warehouse
If the column is defined as not null, then varchar2(1) and char(1) would make no difference. Also, if the columns can accept null values, and no values are entered, there's no difference either. The developers seem to be under the misunderstanding that when a column is defined as char, and no values are entered, it still occupies space equal to the length of the column. Show them the use of the dump function.
Select col1, dump(col1) from tablename;
What the developer would be more concerned about would be with character datatypes defined with lengths more than 1, being blank padded with space when the data length does not match the field length. The comparison semantics change. Hence, I like to see all my columns with varchar2 datatypes.
Also, since we are talking in bytes here, there's always a 1 byte overhead for each field. The only exception is null values at the end of the row. Hence, generally columns likely to contain null values are grouped at the end of the table.
Raj
YTTRI Lisa
<lisa.yttri_at_c To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
nh.com> cc:
Sent by: Subject: char vs. varchar in a data warehouse
root_at_fatcity.
com
June 05, 2002
11:53 AM
Please
respond to
ORACLE-L
Hi -
We are in the process of building a data warehouse and data stores. This will be built on a Sun Solaris (2.8) machine running Oracle 9i R1. Estimates are approximately 180GB.
The developers have defined most of their small character fields as
varchar2
- even the ones of size 1! They tell me that if the column does not have a
value, they do not want to have to deal with a 'space' when manipulating
that value.
Does this make sense? Should I be pursuing this further or shouldn't I
care
as the DBA?
Lisa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Rajesh.Rao_at_jpmchase.com
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: ListGuru_at_fatcity.com (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). Received on Wed Jun 05 2002 - 12:43:25 CDT
 |
 |