Re: index not used in LIKE 'aaa%'

Ted Chyn wrote:
> An nonunique index is defined on patient_acct. There are only 6 rows
> qualified for the selection and why index is not used for query 2.

Please mention also your Oracle Server version. Do you have recent statistics on that table? Do you have histograms?

> 2. index was not used with following sql when additional field appear
> in select clause. full table scan is used for the sql.
>
> select patient_acct,pi_surr_id from pymtitem
> where (PATIENT_ACCT like '613%3713HENSL').

Just a shot in the dark before knowing your statistics/histogram situation: you could define an index on patient_acct that includes pi_surr_id as a second column. Thus, the optimizer would know that it doesn't need a table lookup to fetch the second row if it finds it within the index, so the relative cost for a full scan is higher because chances are higher not having to access the table at all.

But if you have 9iR2, you should perhaps do a

execute dbms_stats.gather_table_stats(NULL,'PATIENT_ACCT',

   estimate_percent=>dbms_stats.auto_sample_size);

and try the second query again.

Regards
Maik Received on Mon Apr 19 2004 - 12:06:34 CDT