Re: Varchar2 vs Char

Hello,
 just my view of it, i am not an expert in Oracle. I have been using it for a while.

 My understanding is that char*acter fields are fixed length and always take room up in the database up to the maximum length. I do know that char*acter fields are always space padded to the maximun length in any code which uses them.
 Varchar2 fields are stored with trailing spaces removed (the length of the field is probably stored in the database). Therefore the performance hit or not will be depending on the length of string data stored in a field. The speed of disk accesses, data transfer speeds , available memory, number of simutaneous users etc.   If you have declared a 200 character field and only store 10 character data in it, then you take a performance hit compared with a 200 character varchar2 field in which you store 10 character strings.

My understanding is that there is no one set of 'rules' as to what sort of fields types to use or what sort of queries to use in any Oracle Application (system). You have to look at the mixtures of data and types of processing to determine the 'optimium' for your particular application (system).

i have some interesting experiences of sites with restricted coding practises. One example is the site which made the rule that all sub-queries must use 'exists' never 'in'. Mostly quite reasonable, however, in some circumstances it is awful and the 'in' code is much quicker!

Tuning any database and associated processing is an art not a science. imo. (you do need to know the science though!). Measuring is a must.

I think your developers are taking a rather limited view of the situation. Howver, they know far more about the particular situation than i do. They may very well be correct.

I suggest you get them to do some measurements for you with representative samples and volumes of 'real' data with both approaches. This will give everyone real information to discuss rather than feelings or opinion. It is certain to be a learning and useful experience no matter what the outcome.   

graham

twod_at_not.valid wrote...

| : > I've been getting feedback from the developers arguing that using varchar2
| : > instead of char when defining a table is taking a performance hit. Does anyone
|
| Where's the performance hit, when they are defining the table ? I'd live with
| that :)
|
| Ask them what is taking a performance hit, get them to prove it to you,
| tell them to tune their code and SQL *and* then tell them that the coding
| standards only allow the use of VARCHAR2 !
|
| IAP
|
| --
| In an attempt to reduce junk email I use an invalid 'From' address.
| My correct email address can can be determined by replacing 'not.valid' with
| 'value.net'

Graham Miller ...
Opinions expressed are mine, they are free, and worth exactly what they cost. Received on Sat May 10 1997 - 00:00:00 CDT