Re: index not used in LIKE 'aaa%'

"Ted Chyn" <tedchyn_at_yahoo.com> wrote in message news:44a19320.0404190856.79c7c92c_at_posting.google.com...
> I have a table with 5 million rows with cbo.
>
> 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.
>
>
> 1. index used with following sql.
> select patient_acct from pymtitem
> where (PATIENT_ACCT like '613%3713HENSL')
>
> 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').
>
>
> thanks ted

Couple of things:

• In #1 above, the index is definitely used since the whole query can be satisfied by using the index alone. The same is not true in case #2 above.
• The usage of index is also quite sensitive to the setting of the init ora parameters: optimizer_index_cost_adj and optimizer_index_caching

   If you never changed these parameters from their default value of 100 and 0, then it could    a major contributing factor in why the index is not being used. However, a lot of testing is required

   if you plan on changing them now (to a more realistic value for your env).

   For example: Try your query after setting the init ora parameters to the following:    alter session set optimizer_index_caching = 70;    alter session set optimizer_index_cost_adj = 20;

   (Note: I'm not suggesting that 70 / 20 are good values .. you have to find the sweet spot for your env .. yourself)

• Although the query returns only 6 out of million rows. Even if you create a histogram on the patient_acct column, oracle still would only be able to use the '613%' part of the like clause in determing the cardinality of the predicate. If using just '613%' yields very few rows, then you might benefit in generating histogram for the patient_acct column (again adequate testing is required). Histogram can be generated like this: exec dbms_stats.gather_table_stats(user, 'PYMTITEM',cascade=>true, method_opt=>'for all indexed columns size 200')
• Be aware of the cursor_sharing parameters in case you plan on using histograms (if they are set to force or similar for your env). Then creating histogram might result in uneven behavior of the query above since 9i onwards bind variable peeking is done only on the first execution.
• Last/Worst resort would be to use a index hint.

HTH Anurag Received on Mon Apr 19 2004 - 15:55:42 CDT