Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<sl0i4095oenqir1pqu3plo3umgu4pqt2ce_at_4ax.com>...
> On 5 Mar 2004 14:26:56 -0800, mikelbell2000_at_yahoo.com (Mike L. Bell)
> wrote:
>
> >I recently inherited management of several Oracle databases. By
> >background I am not an Oracle DBA, but have over a decade of
> >experience using other RDBMSs such as Informix and DB2. Eager to learn
> >new Oracle skills I started taking inventory of the databases. One of
> >the outstanding things that caught my eye was the use of VARCHAR for
> >all of the character based data fields. Not a single fixed CHAR field
> >to be found. When I asked around, the developers told me, that's the
> >way you do it in Oracle.
> >
> >In my DB2 databases, there are considerations with both storage and
> >performance when choosing VARCHAR over CHAR. There is a 4-byte
> >overhead for each row (2 for the data page offset and 2 for the
> >length). I am curious to understand the underlying implementation on
> >the Oracle side. What overhead is incurred (if any) for VARCHAR? And
> >are there any similar performance considerations (regarding querying,
> >updating and indexing)?
> >
> >The limited docs that I have don't mention anything about this area.
> >I'm anxious to come up to speed with my newly inherited environment.
> >
> >Thanks,
> >Mike
>
> Refer to the Oracle Reference Manual and the SQL reference manual with
> respect to datatypes. All of it is online at http://tahiti.oracle.com
>
> The ordinary string datatype is a VARCHAR2, not a VARCHAR.
> A VARCHAR2 has a two byte overhead for storing the length.
> The VARCHAR2 is using variable length semantics, VARCHAR was meant to
> have fixed length semantics, though I am not sure whether Oracle ever
> implemented it.
> CHAR is fixed length, space padded.
CHAR also has a 2 byte overhead for storing the length. Therefore
there is no space-saving advantage in using CHAR instead of VARCHAR2
even when the data is in fact all of a fixed length.
Received on Tue Mar 09 2004 - 06:29:56 CST
