Re: Weird advice : keep varchar2 under 255 bytes ???

On Tue, 12 Apr 2005 18:11:37 +0200, Michel Cadot wrote:

>
> "Jerome" <vitalismanREMOVETHAT_at_gmail.com> a écrit dans le message de
> news:pan.2005.04.12.15.24.23.704000_at_gmail.com...
> | On Mon, 11 Apr 2005 23:51:43 -0700, Le JeanMimi wrote:
> |
> | > First, thank you all for your help.
> | >
> | >> From the top of my head: for a column with a maximum length longer
> | >> than 255 Oracle will use 2 length bytes, otherwise only one. So there
> | >> is a difference, albeit little.
> | >>
> | >> Jaap.
> | >
> | > Another question :
> | > If we store a string whose length is < 255 bytes in a varchar2 column
> | > whose length is > 255 bytes, will Oracle use 2 bytes or 1 byte for the
> | > length heading ?
> |
> | Salut Jean-Michel !
> |
> | This is an interesting question. I've done some tests using 'alter system
> | dump datafile ...' and the results are a bit surprising:
> |
> | 9.2.0.1.0 on Windows 2000 ;-( (very sorry about that, I don't have
> | a decent OS at hand at the moment)
> |
> | SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET
> | ';
> |
> | VALUE
> | ----------------------------------------
> | WE8MSWIN1252
> |
> | SQL> create table t(v varchar2(4000));
> |
> | Table created.
> |
> | SQL> insert into t values('a');
> |
> | 1 row created.
> |
> | SQL> insert into t values(rpad('a',255,'a'));
> |
> | 1 row created.
> |
> | SQL> insert into t values(rpad('a',4000,'a'));
> |
> | 1 row created.
> |
> | + additional inserts with the following string lengths: 100, 254, 250,
> | 252, 251
> |
> |
> | Extract from the related block dump file:
> |
> | block_row_dump:
> | tab 0, row 0, @0x1f9b
> | tl: 5 fb: --H-FL-- lb: 0x0 cc: 1
> | col 0: [ 1] 61
> | tab 0, row 1, @0x1e96
> | tl: 261 fb: --H-FL-- lb: 0x0 cc: 1
> | col 0: [255]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61
> | tab 0, row 2, @0xef0
> | tl: 4006 fb: --H-FL-- lb: 0x0 cc: 1
> | col 0: [4000]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | ...
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | tab 0, row 3, @0xe88
> | tl: 104 fb: --H-FL-- lb: 0x0 cc: 1
> | col 0: [100]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | tab 0, row 4, @0xd84
> | tl: 260 fb: --H-FL-- lb: 0x0 cc: 1
> | col 0: [254]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | ...
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61 61 61
> | tab 0, row 5, @0xc86
> | tl: 254 fb: --H-FL-- lb: 0x0 cc: 1
> | col 0: [250]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | ...
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | tab 0, row 6, @0xb84
> | tl: 258 fb: --H-FL-- lb: 0x1 cc: 1
> | col 0: [252]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | ...
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61 61
> | tab 0, row 7, @0xa83
> | tl: 257 fb: --H-FL-- lb: 0x2 cc: 1
> | col 0: [251]
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | ...
> | 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61
> | 61
> | end_of_block_dump
> | End dump data blocks tsn: 15 file#: 14 minblk 1479 maxblk 1479
> |
> | So apparently (we can't be sure since the block dump format is not
> | documented), in this environment, the row size in this single column table
> | is, in bytes:
> |
> | 4+length(v) if length(v)<=250
> | 6+length(v) if length(v)>250
> |
> | In this environment the answer to your question seems to be: Oracle uses 2
> | (not just 1) more bytes for the string length only if it is greater than
> | 250 (not 255).
> |
> |
> | Regards,
> | Jerome
>
> Good test but this in the doc since (at least) version7.
> For instance, in 9.2, you'll find it in section "Row Format and Size"
> of Database Concepts Chapter 10 "Schema Objects":
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#2747
>
> <quote>
> ...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...
> </quote>
>
> Regards
> Michel Cadot

Merci Michel for the pointer. I didn't remember this was documented.

Jerome Received on Wed Apr 13 2005 - 03:04:09 CDT