Re: Index Creation Question - Which is More Efficient?

Dereck L. Dietz wrote:
> "Matthias Hoys" <anti_at_spam.com> wrote in message
> news:44ccd3ef$0$1211$ba620e4c_at_news.skynet.be...
> >
> > "Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message
> > news:Eo3zg.183675$F_3.68439_at_newssvr29.news.prodigy.net...
> > > According to the documentation I have (Oracle 10g Administration I Study
> > > Guide, page 153), TRIM isn't needed when storing VARCHAR2 characters in
> a
> > > database since a VARCHAR type only stores the amount of data that is
> > > actually used. You're TRIMming stuff that isn't stored in the database
> in
> > > the first place. Conversely CHAR datatypes will always store the full
> > > size
> > > of the field, right padding with spaces as necessary. So TRIMming a
> CHAR
> > > character is useless since the data will be right-padded with spaces
> when
> > > stored anyways.
> > >
> > Hmmm ... I think they mean when you do something like this :
> >
> > INSERT INTO MyTable (mystring) values (' string ');
> >
> > If you want to remove the white spaces before the INSERT operation, you
> need
> > to perform a TRIM on it, right ? But, like some other poster mentioned,
> it's
> > better to correct this in the application before the insert statement is
> > generated.
> >
> No they meant the way Oracle stores datatypes. According to my
> documentation Oracle doesn't store trailing spaces for VARCHAR characters so
> TRIMming them before inserting is not needed. If a field is defined as
> VARCHAR2(100) and the value stored in it is "TEST" only 4 characters are
> stored. However, if the field is defined as CHAR(100) then even if the
> value stored is "TEST" the full 100 characters are stored with trailing
> spaces making up the difference.

It could be a bug in the documentation that you have. Your interpretation is 95% correct. What Matthias Hoys is suggesting is that if you insert ' abc ' into a VARCHAR2 column that is defined as VARCHAR2(100), Oracle will in fact store the 5 spaces before abc and the 5 spaces after abc (13 characters total). The documentation that you have states that Oracle will not right pad a VARCHAR2 column with spaces so that it is exactly 100 characters as it will with a CHAR column, which is correct. However, it will not strip off trailing nor leading spaces, since those spaces could be critical to the functioning of the program. The problem with this, of course is that ' abc ' does not equal 'abc' in queries. That is a problem that can be corrected in the application code before the data is inserted into the table.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Jul 30 2006 - 13:24:05 CDT