Re: Function Based Indexes with in clause?

Hi Galen,

I had a bit of a play:

I tried to setup a similar example to yourself with a table with a function based index. The first query is based on two values that are very rare in the table.

SQL> select /*+ index(names uppername) */ * from names where upper(name) in ('A', 'B');

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=5)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NAMES' (Cost=3 Card=1 By

          tes=5)

   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP OR
   4    3         BITMAP CONVERSION (FROM ROWIDS)
   5    4           INDEX (RANGE SCAN) OF 'UPPERNAME' (NON-UNIQUE) (Co
          st=1)

   6    3         BITMAP CONVERSION (FROM ROWIDS)
   7    6           INDEX (RANGE SCAN) OF 'UPPERNAME' (NON-UNIQUE) (Co
          st=1)

It used the index as requested.

The next example is based on one value being very common in the table ('Bowie' of course :)

SQL> select /*+ index(names uppername) */ * from names where upper(name) in ('A', 'BOWIE');

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=55 Card=2065 Bytes=1
          0325)

   1    0   TABLE ACCESS (FULL) OF 'NAMES' (Cost=55 Card=2065 Bytes=10
          325)

The optimizer basically said, I know you requested the use of the index again, but this time the value of 'BOWIE' is about 12% of the data so go and get nicked !!

This therefore leads me to believe that Oracle doesn't like the distribution of the data in your in list. Are you sure that the value of '80172/1' in particular is not very common (over a couple of percent in your data) ? I can only suggest Oracle is doing this because it feels that the FTS is the way to go. You may want to re-analyze with more buckets in case the default is not providing the required distribution statistics for this column.

Sorry I can't be of any more help :(

Cheers

Richard

Galen Boyer wrote:
>
> On 9 Aug 2002, billiauk_at_yahoo.co.uk wrote:
> > Galen
> >
> > Try building a histogram (default 75-ish buckets should be
> > fine) on your indexed column and see if the CBO uses the index
> > then...
>
> I believe the following creates this histogram you are
> mentioning? (This post has the real table and query where the
> previous post was trying to use a Tom Kyte example so it would be
> less suspect that I did something wrong)
>
> ORA>analyze table TEST_PART0222_TBL compute statistics
> for all indexed columns;
>
> Table analyzed.
>
> ORA>explain plan set statement_id = 'GBOYERSPLAN' into plan_table for
> SELECT /*+ index(t lot_color_ix) */
> LOT || '/' || COLOR AS CLIENTMERCHID_COLOR,
> SUM(net_sales_units) AS SUMSALESUNITS,
> calendar_dt AS CALENDARDATE,
> COUNT(DISTINCT store_nbr) AS STORECOUNT,
> SUM(receipt_units)/COUNT(DISTINCT store_nbr) AS AVGRECEIPTUNITS
> FROM ELM.TEST_PART0222_TBL t
> WHERE LOT || '/' || COLOR in ('80675/2','80172/1')
> GROUP BY LOT || '/' || COLOR, calendar_dt
> ;
>
> ORA>@explain_galen
>
> 1 SELECT STATEMENT
> 2 SORT GROUP BY
> 3 TABLE ACCESS FULL TEST_PART0222_TBL
>
> This table is 3G I usually cut out my prompt and other ex. With
> a single equality, I get the index used.
>
> ORA>explain plan set statement_id = 'GBOYERSPLAN' into plan_table for
> SELECT /*+ index(t lot_color_ix) */
> LOT || '/' || COLOR AS CLIENTMERCHID_COLOR,
> SUM(net_sales_units) AS SUMSALESUNITS,
> calendar_dt AS CALENDARDATE,
> COUNT(DISTINCT store_nbr) AS STORECOUNT,
> SUM(receipt_units)/COUNT(DISTINCT store_nbr) AS AVGRECEIPTUNITS
> FROM ELM.TEST_PART0222_TBL t
> WHERE LOT || '/' || COLOR = '80675/2'
> GROUP BY LOT || '/' || COLOR, calendar_dt
> ;
>
> Explained.
>
> ORA>@explain_galen
>
> 1 SELECT STATEMENT
> 2 SORT GROUP BY
> 3 TABLE ACCESS BY INDEX ROWID TEST_PART0222_TBL
> 4 INDEX RANGE SCAN LOT_COLOR_IX
>
> --
> Galen Boyer