Re: Function Based Indexes with in clause?

Hi Galen,

I had more of a play :)

I tried this.

SQL> alter session set query_rewrite_enabled=true;

Session altered.

SQL> set autotrace traceonly exp
SQL> analyze table names delete statistics;

Table analyzed.

I first got rid of the all statistics. Then re-run the query;

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

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=51 Card=288 Bytes=20
          16)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'NAMES' (Cost=51 Card=288
           Bytes=2016)

   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)

Now I got it to use the index. Because there's a hint, it's still using the CBO.

Now of course my environment is totally different to yours so who know what will happen to your query. In my case, the index is *NOT* a good path but I forced Oracle to use it anyway. I thought it interesting.

Might be worth a go to delete all the statistics and see what happens. **This goes against everything I would normally recommend** but in my case what the heck. In your case maybe it might be a ugly quick fix (hate to think what will happen to all your other queries on this table though, yuck !!)

Regards

Richard

Galen Boyer wrote:
>
> On Sat, 10 Aug 2002, Richard.Foote_at_oracle.com wrote:
>
> > 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) ?
>
> Yes, I'm positive.
>
> ORA>SELECT LOT,COLOR,COUNT(*)
> FROM ELM.TEST_PART0222_TBL t
> WHERE LOT in (80676,80172) and COLOR in (2,1)
> GROUP BY LOT,COLOR
> ;
>
> LOT COLOR COUNT(*)
> ---------- ---------- ----------
> 80172 1 11
>
> ORA>select count(*) from TEST_PART0222_TBL;
>
> COUNT(*)
> ----------
> 37755223
>
> Is there anything to the values not being unique or not existing
> in the IN clause? This doesn't seem like it would be an issue
> and when I changed the values of the in clause from
> ('80675/2','80172/1') TO ('80172/1','59263/0') where both sets of
> LOT/COLOR exist, the plan is still full table scan.
>
> > I can only suggest Oracle is doing this because it feels that
> > the FTS is the way to go.
>
> It is wrong, but I'm sure its cause I haven't inserted the right
> memory component into Oracle's brain.
>
> > You may want to re-analyze with more buckets in case the
> > default is not providing the required distribution statistics
> > for this column.
>
> Alright, time to read this bucket thing. We don't use the
> histogram capability here at all. On this type of query,
> expertise in this Oracle feature would be helpful. I'll read and
> talk to the DBA's.
>
> --
> Galen Boyer