Re: Index Creation Question - Which is More Efficient?

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1154136980.245073.261650_at_p79g2000cwp.googlegroups.com...
> This particular index might be useful in the following situations:
> SELECT
> *
> FROM
> T_HB_UPDATE_RESULTS
> WHERE
> PERSON_ID||TRIM(TYPE)||"DATE" = 'ABC123'||'HOURLY'||TRUNC(SYSDATE);
>
> or
>
> SELECT
> *
> FROM
> T_HB_UPDATE_RESULTS
> WHERE
> PERSON_ID||TRIM(TYPE)||"DATE" LIKE 'ABC123%';
>
> How often would you see code like that in a well written application?
>
> SELECT
> *
> FROM
> T_HB_UPDATE_RESULTS
> WHERE
> PERSON_ID='ABC123'
> AND TRIM(TYPE)='HOURLY'
> AND "DATE"=TRUNC(SYSDATE);
>
> The above is a bit easier to read. With your suggestion of indexing
> the columns PERSON_ID, TYPE, "DATE", this query _might_ use your index,
> or might not - the inclusion of the function TRIM would prevent Oracle
> from using the full index. It _might_ still use part of the index to
> find where PERSON_ID='ABC123'.
>
> See: http://asktom.oracle.com/~tkyte/article1/ for additional
> information.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

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. Received on Sat Jul 29 2006 - 19:14:39 CDT