Re: Index Creation Question - Which is More Efficient?

"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...
> >
> > "Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
> > news:1154267985.140826.173580_at_m79g2000cwm.googlegroups.com...
> >> Dereck L. Dietz wrote:
> >> > Your question "How often would you see code like that in a well
written
> >> > application?" is the key to this. This code I'm reworking is NOT
> >> > well-written at all. It was actually written by non-programmers who
> >> > consistently TRIM VARCHAR2 fields before inserting into tables. My
> > opinion
> >> > is someone read about the TRIM function and it became their flavor of
> > the
> >> > day and they have definitely overused it (they even TRIM the
person_id
> > field
> >> > which is NUMERIC). In the code I included there is really no reason
to
> > use
> >> > TRIM at all.
> >> >
> >> > I'm rewriting processes ever so slowly and will be getting rid of
> > indexes
> >> > and code such as included above. I just wanted written confirmation
> > from
> >> > someone other than me to show in case someone there tries to fight me
> >> > on
> > my
> >> > changes.
> >> >
> >> > Thanks.
> >>
> >> There is nothing wrong with performing a TRIM of VARCHAR2 fields when
> >> inserting into the database tables, although this could very likely be
> >> handled within the program rather than requiring the Oracle database to
> >> perform the function. However, applying TRIM to the table columns in
> >> the WHERE clause is generally not a good idea, especially if the column
> >> values are already pre-trimmed when the data is inserted into the
> >> table.
> >>
> >> Creating function based indexes on TRIM(MY_COLUMN) can help improve
> >> select performance if TRIM(MY_COLUMN) is included in the WHERE clause,
> >> but it is better to determine if TRIM(MY_COLUMN)= is required, or if
> >> MY_COLUMN= will work just as well.
> >>
> >> Charles Hooper
> >> PC Support Specialist
> >> K&M Machine-Fabricating, Inc.
> >>
> >
> > 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. Received on Sun Jul 30 2006 - 12:26:15 CDT