Re: VARCHAR length

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 9 Oct 2002 06:36:37 +1000
Message-ID: <zdHo9.48840$g9.140869@newsfeeds.bigpond.com>

Well, Oracle's doco. is not exactly brimming with information on the subject (at least, not information that's readily findable). Here's what the Oracle Concepts manual says:

A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

Notice that it talks about the *data* component being variable if the datatype is variable, but simply says blandly that the column length bytes are either 1 or 3, no exceptions mentioned. And the last time I did a block dump, that was indeed the case.

Now I'm not so old as to be able to remember Oracle 7 very well (we'd have to ask Richard Foote if we want to go back *that* far), but I could have sworn that the same was true there as well.

Regards
HJR "Karsten Farell" <kfarrell_at_medimpact.com> wrote in message news:eaHo9.1596$i17.195196079_at_newssvr13.news.prodigy.com...
> Howard J. Rogers wrote:
> > I hope you're not claiming that CHAR datatypes don't use length bytes?
> >
> > They do.
> >
> > Regards
> > HJR
>
> Correct me if I'm wrong, but it's been quite a while since I attended an
> oracle architecture class. As you probably know, training is one of the
> first things to go in a shrinking budget ... and new DBAs get trained
> before old-timers - as it should be, I suppose, since they have more to
> learn. So I haven't heard the lecture since Oracle7 first came out.
> Something tells me Oracle just might have done a little work on their db
> since then, eh?
>
> I thought the length of a CHAR field was stored in the data dictionary
> (since it never changes) and the length of a VARCHAR2 field was stored
> in the database row (since it potentially changes). Therefore, the
> starting byte position of each CHAR field can be determined at SQL parse
> time (if there are no variable-length fields in front of it); but the
> starting position of VARCHAR2 fields cannot be determined until the row
> is read into the buffer.
>
Received on Tue Oct 08 2002 - 15:36:37 CDT