Re: NLS_LENGTH_SEMANTICS=CHAR and column storage size

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 02 Nov 2005 18:29:04 +0100
Message-ID: <4368f796$0$21958$9b4e6d93@newsread2.arcor-online.net>

Maxim Demenko schrieb:
> Laurenz Albe schrieb:
>

>> My company considers using UNICODE databases with
>> NLS_LENGTH_SEMANTICS=CHAR to avoid problems inserting a value like
>> 'Länge' into a CHAR(5) or VARCHAR2(5) column.
>>  
>> Now I am concerned about space requirements of such a database
>> because I don't understand enough about the storage of such columns.
>>  
>> DATA_LENGTH for a CHAR(5) or VARCHAR2(5) column will be 20.
>>  
>> - Does that mean that there will be 20 bytes reserved for this column
>>   in each row?
>> - Is it possible that migrated databases might grow by a factor 4?
>> - Is there documentation how such columns are stored in Oracle?
>>  
>> Thank you,
>> Laurenz Albe

>
>
>
> In case of varchar2 you allocate as much space as byte representation of
> your data need ( i.e for 'Länge' 6 bytes assumed that 'ä' needs 2 bytes).
> For char columns space will be preallocated to get the required amount
> of characters in worst case - i.e. 20 bytes per column for char(5 char).
> You should take in account, that limits of 2000 bytes resp. 4000 bytes
> are still valid, that means for char column for example char(500 char)
> will allocate 2000 bytes and char(501 char) will allocate 2000 bytes as
> well. In latter column you will be not able insert values that takes
> more than 2000 bytes ( if every one character from 501 require 4 byte to
> be stored ) if even the string have 501 characters length.
>
> Best regards
>
> Maxim

I would like to correct myself regarding the calculation for space required for char allocation. In opposite to single byte databases the amount of space allocation for char can vary, because it depends on the data stored.
It means char(5 char) will require 20 bytes *only* if you store there a string of 5 fourbytes characters. In case you store strings which are shorter than maximum length defined , the space will be the byte representation of *blank padded* data, i.e. 'ä' will require 6 bytes in a char(5 char) column ( assumed 'ä' needs 2 bytes) and not 20.

Best regards

Maxim Received on Wed Nov 02 2005 - 11:29:04 CST