Re: Underlying Implementation of VARCHAR data types

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.

--
Sybrand Bakker, Senior Oracle DBA