Re: CHAR() vs VARCHAR@()

> Paul T. Allison wrote:
> Actually, every field with length greater than 250 takes
> 2 extra space bytes. This could be significant in a large table.
>
> Ian MacGregor replied:
>
> Why 250, not 255. One can certainly store '2000' in two bytes. What's the
> purpose of the third?... To store whether the column lenght is stored in
> one or two bytes ?

Exactly.

Lengths are stored as byte or 3 bytes. However, Oracle will only use the three bytes ones the actual stored length of the data exceeds 250 bytes.

When this occurs, the first byte is set to 254 to indicate that the next two bytes are the actual data length.

A varchar2() holding 250 bytes will have its length given by:

        FA
A varchar2() holding 251 bytes will have it length given by:

        FE 00 FB
A varchar2() holding 258 bytes will have it length given by:

        FE 01 02

Since CHAR() is always space padded, any declaration of char(N) where N > 250 will result in a 'redundant' FE 00 being prepended to the 'actual' length byte. Personally, with this algorithm, I would have expected char() (and raw()) to be restricted to 250 bytes, or allowed to be the same maximum as varchar(2).

Interestingly, there is an item somewhere in one of the manuals that states the break from 1 to 3 bytes occurs at 127 bytes, and I think that I tested this out on one version of Oracle some time ago where it was true. Can't rememebr the version though.

Most recent tests on: 7.0.16, HP-UX

---
Jonathan Lewis
ora_mail_at_jlcomp.demon.co.uk Received on Wed Feb 07 1996 - 06:07:19 CST