Re: Underlying Implementation of VARCHAR data types

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.

And for a very good reason.

> 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

I urge people to never use CHAR for any reason and here's why. The fixed-length CHAR pads spaces no matter what you put into it. So a CHAR(10) with one character is 10 bytes while whe VARCHAR2(10) with one charater is 3 bytes. Storage is of little consequence in these days of inexpensive disk.

What is of consequence is those spaces used to pad out the other nine bytes. They make string comparisons difficult and expensive.

In Oracle VARCHAR (as opposed to VARCHAR2) is a C data type ... not a SQL or PL/SQL data type.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)