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@c To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
nh.com> cc:
Sent by: Subject: char vs. varchar in a data
warehouse
root@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: [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).
