Re: Index Creation Question - Which is More Efficient?

Dereck L. Dietz wrote:
> I'm trying to rewrite some existing code and keep coming across indexes
> being created similar to this:
>
> CREATE INDEX TSYSCONTYPRES_I
> ON T_HB_UPDATE_RESULTS(PERSON_ID||TRIM(TYPE)||"DATE");
>
> Other than the fact there are reserved words in the code would that index be
> efficient or would one defined like before (using the same fields just for
> comparison purposes) be more efficient:
>
> CREATE INDEX TSYSCONTYPRES_I
> ON T_HB_UPDATE_RESULTS(PERSON_ID,
> TYPE,
> "DATE");
>
> Thanks.

Looks like a function based index that has limited usefulness, beyond slowing down the rate of inserts into the table. Before deciding on whether or not to keep the index, determine if the index is being used at all. Turn on tracing for the database session, then use tkprof to parse the trace file. If TSYSCONTYPRES_I appears in the tkprof plan output, then the index might be useful, or at least usable.

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. Received on Fri Jul 28 2006 - 20:36:20 CDT