Re: Underlying Implementation of VARCHAR data types

> > 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.

Sybrand,

Can you please explain how did you get that "A VARCHAR2 has a two byte overhead for storing the length"?

Here is what I found:

SQL> create table t ( x varchar2(1000) );

Table created.

SQL> insert into t(x) values('*');

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select avg_row_len from user_tables where table_name='T';

AVG_ROW_LEN

          5

SQL> delete from t;

1 row deleted.

SQL> insert into t(x) values(rpad('*', 250, '*'));

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select avg_row_len from user_tables where table_name='T';

AVG_ROW_LEN

        254

SQL> delete from t;

1 row deleted.

SQL> insert into t(x) values(rpad('*', 251, '*'));

1 row created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select avg_row_len from user_tables where table_name='T';

AVG_ROW_LEN

        257

I know for table t there are 3 bytes(fb, lb, cc) overhead in row header, so I get the conclusion
that value length <= 250, the overhead is 1, value length > 250, the overhead is 3. Char type has
same behavior.

I did this test on 9.2.0.3.

Please let me know if something wrong in my test.

Thanks,
DX Received on Thu Apr 08 2004 - 15:21:03 CDT