RE: RE: Get max length of data for a column

CHAR is always stored fixed length - with padding. So variable X as CHAR(10) with value '123' would be stored as '123       ' whereas variable Y as VARCHAR2(10) would be stored as '123'.

Since an Oracle data block is either 4K or 8K having lots of CHAR columns can quickly fill up blocks (potentially with empty space)  - so fewer rows per block mean lower buffer cache hit ratio and other things.

My personal "best practice" is to only use CHAR for relatively short needs such as phone number or SSN where I need columns to have exactly N positions. But others have different beliefs .....

Bert



From: [email protected] [mailto:[email protected]] On Behalf Of Norman Dunbar
Sent: Wednesday, April 18, 2012 2:26 AM
To: [email protected]
Subject: Re: [toad] RE: Get max length of data for a column



Morning Mike,

> Thanks for that clarification. To be honest, we don't really use CHAR here
> anymore, which is why I've either forgotten that important piece of
> information, or (worse) never knew it.
Years ago, I wrote a "data obfuscation" routine with simply replaced
certain columns with random data generated by DBMS_RANDOM. All my CHAR
columns ended up being exactly the defined length! That's when I noticed
it. I ended up using VSIZE() to get the length of the TRIM()med CHAR
columns. Sort of stuck in my "cache" since then.

> Out of interest, is there any reason
> why one would want to use CHAR rather than VARCHAR?
I'm not sure. I use them occasionally myself. Just for internal space
saving. A VARCHAR2 has a hidden size setting. If the column is 100 long
and you store 40 in it, there's a counter internally that says "this is
how long the data is". Not a great reason, but if you have flag columns
- gender, for example, that are only one character long, you don't need
the counter internally.

There are probably other good reasons for using CHAR, but I can't think
of any off hand. Maybe Bert knows - he knows lots of good stuff!

--
Cheers,
Norm. [TeamT]