Re: index not used in LIKE 'aaa%'

anurag,

I tried set optimize_index_cahing and cost_adj and it did not help. even index hint did not work and the only thing worked is to use rule hint see below.

There are a big difference in terms of performance.

  1 select /*+ rule */ patient_acct,pi_surr_id from pymtitem   2* where patient_acct like '613%3713HENSL' SQL> / 6 rows selected.

Execution Plan

   0 SELECT STATEMENT Optimizer=HINT: RULE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PYMTITEM'    2 1 INDEX (RANGE SCAN) OF 'PAYITM_PACCT_IDX' (NON-UNIQUE) Statistics

          0  recursive calls
          0  db block gets
        194  consistent gets
        193  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

===================

  1 select patient_acct,pi_surr_id from pymtitem   2* where patient_acct like '613%3713HENSL' SQL> / 6 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6367 Card=343416 Byt
          es=7555152)

   1    0   TABLE ACCESS (FULL) OF 'PYMTITEM' (Cost=6367 Card=343416 B
          ytes=7555152)

Statistics

          0  recursive calls
          0  db block gets
     104466  consistent gets
     104421  physical reads
          0  redo size
        684  bytes sent via SQL*Net to client
        651  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<i1Xgc.16819$L31.5029_at_nwrddc01.gnilink.net>...
> "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 Tue Apr 20 2004 - 17:49:38 CDT