Re: Index Creation Question - Which is More Efficient?

"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. Received on Sun Jul 30 2006 - 09:31:00 CDT