Re: NLS_LENGTH_SEMANTICS=CHAR and column storage size

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 Received on Wed Nov 02 2005 - 10:21:02 CST