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

From: Jerome <vitalismanREMOVETHAT_at_gmail.com>
Date: Tue, 12 Apr 2005 17:24:24 +0200
Message-ID: <pan.2005.04.12.15.24.23.704000@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 Received on Tue Apr 12 2005 - 10:24:24 CDT