Re: Oracle database configuration options

Paul Brewer (paulb_at_pbrewer.demon.co.uk) wrote:
> In article <$pVmEEAfQMcyEwVk_at_jimsmith.demon.co.uk>, Jim Smith
> <jim_at_jimsmith.demon.co.uk> writes
> <snip>
> >Oracle7 provides 2 character datatypes - CHAR is fixed length and
> >VARCHAR2 is variable length. This is the behaviour required by the ANSI
> >SQL standard. (there is also VARCHAR which is identical to VARCHAR2, but
> >which Oracle recommends should not be used.
> <snip>
 

> We're just starting out moving our legacy applications to Oracle.
> One design thought which has been bothering me for a while...
> Most of the stuff I've read says basically: 'Use CHAR for fixed length
> stuff, and VARCHAR2 for variable'.
> Seemed sensible to us, but we then gave ourselves problems in dynamic
> queries, where, for example, a CHAR(4) column contained 'ABC '. Of
> couse, we had to remember to space-fill user input before constructing
> 'where' clauses.
> So we thought 'OK, let's just use VARCHAR2 all the time', but were
> advised that this wasn't a good idea, as there would be a significant
> performance impact when using VARCHAR2 columns in indexes, so we didn't
> bother.
> We're not yet too far down the road to change our approach, and would
> welcome any comments or suggestions on this.
> TIA.
 
> Paul Brewer

There has been a thread called "A question of principle" running in comp.databases.oracle.misc which favours using varchar2 over char, especially if you are using spaces to represent a null column.

To my knowledge there is no problem with varchar2 and performance, and if you are using or considering PRO*C, do yourself a favour and use varchar2, as you won't have the cumbersome bind/unbind processes to convert to/from C strings.

--

Greg.
---------------------------------------------------
All hail the three great virtues of the programmer:
Laziness, impatience and hubris.

echo '[q]sa[ln0=aln256%Pln256/snlbx]sb3135071790101768542287578439snlbxq'|dc