Re: VARCHAR2

S.M.Victor (SMVICTOR_at_concentric.net) wrote:
: roosens wrote:
:
: >hangyk_at_singnet.com.sg (Jason Hang) wrote:
: >>Gregory R Mattes <mattes+_at_andrew.cmu.edu> wrote:
: >>> I have a question concerning the VARCHAR2 datatype. i have a
: >>>reference book that tells me that there is no penalty for declaring a
: >>>large maximum value for a VARCHAR2 and storing a relatively small string
: >>>in it. it says that the largest length of a VARCHAR2 is 2000
: >>>characters. my question is this: why not simply declare all string
: >>>columns as VARCHAR2( 2000 ) ??
 

: >>> is this in fact the popular way to do things? if not, why? is there
: >>>a speed hit incurred in searches involving VARCHAR2's as compared with
: >>>fixed width CHAR's? why do fixed with CHAR's exist if there is no
: >>>penalty for using VARCHAR2's?
: >>
: >>I don't know about the penalties for declaring CHAR and VARCHAR2.
: >>But I can sure tell you that it sure pays to be consistent.
: >>I have a lot of problems when comparing variables and columns when
: >>both are not of the same type, be it CHAR or VARCHAR2.
: >>The best is to stick to one of them, and by rightfully... stick to
: >>VARCHAR2 as recommended.
:
:
: >I can confirm this answer and add:
: >the difference is to be seen when comparing trailing spaces :
: >'ABC ' = 'ABC' with VARCHAR2
:
:
: There is a penalty of allocating extra space whenever the size of the
: value in VARCHAR2 field grows ( during updates ).
: No such penalty for CHAR fields.
: No idea how bad it is in oracle. Wish Oracle manuals give more
: details about it.
:
:

But the penalty is worth the price when you consider that you can rebuild the table anyway periodically. Verses the wasted extra space for all the trailing spaces and the headaches introduced trying to match things like 'ABC ' = 'ABC' or doing rtrims on indexed fields which causes full table scans. I know which I'd choose anyday ...

-- 

+------------------------------------------------+
| Jill Stephenson - Tortuga Technologies Pty Ltd |
| 36 Alderley Avenue, Alderley QLD 4051          |
|                                                |
| Phone: (07) 3356 3076                          |
| Email: jill_at_tortuga.com.au                     |
+------------------------------------------------+