Re: Index Creation Question - Which is More Efficient?

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. Received on Sun Jul 30 2006 - 08:59:45 CDT