My confrontations with oracle

One of the challenges in working with Oracle and Globalization is in identifying how much space oracle requires to store a particular alphabet,especially when the database uses a Unicode Character Set. The Unicode DB Characterset AL32UTF8 is a multi byte characterset. For an ASCII character, oracle(with AL32UTF8) need only 1 byte, where as for a Japanese character (hiragana,katakana or kanji), it would require either 3 or 4 bytes. Most of the Japanese characters are 3 bytes, but as more number of kanaji’s are accepeted for daily use, it becomes evident that oracle would require more bytes to store them.

To see how much space a character occupies, one can use the oracle vsize(‘<string>’) or dump(’string’) functions. Both these functions gives the size in bytes.

Let’s see how much space(in bytes) Oracle Db with AL32UTF8 characterset requires tp store the word ‘tokyo’ in English and Japanese (Hiragana, Full Width Katakana, Half Width Katakana and Kanji).

English
select vsize(‘tokyo’) from dual;
5
select dump(‘tokyo’) from dual;
Typ=96 Len=5: 116,111,107,121,111

Japanese-Hiragana
select vsize(‘とうきょう’) from dual;
15
select dump(‘とうきょう’) from dual;
Typ=96 Len=15: 227,129,168,227,129,134,227,129,141,227,130,135,227,129,134

Japanese-Full Width Katakana
select vsize(‘トウキョウ’) from dual;
15
select dump(‘トウキョウ’) from dual;
Typ=96 Len=15: 227,131,136,227,130,166,227,130,173,227,131,167,227,130,166

Japanese-Half Width Katakana
select vsize(‘ﾄｳｷｮｳ’) from dual;
15
select dump(‘ﾄｳｷｮｳ’) from dual;
Typ=96 Len=15: 239,190,132,239,189,179,239,189,183,239,189,174,239,189,179

Japanese-Kanji
select vsize(‘東京’) from dual;
6
select dump(‘東京’) from dual;
Typ=96 Len=6: 230,157,177,228,186,172

My confrontations with oracle

November 4, 2008

Index

November 16, 2009

Space required by oracle to store a character/string

Archives

Blog Stats

Meta

Recent Posts

Blogroll

My Visitors

November 2009
S	M	T	W	T	F	S
« Oct
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30