Re: Index Creation Question - Which is More Efficient?

"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. Received on Sun Jul 30 2006 - 10:44:47 CDT