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

"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 Received on Tue Apr 12 2005 - 11:11:37 CDT