Re: Function Based Indexes with in clause?

Hi Galen,

And just to bring this to a natural conclusion (in my particular instance):

SQL> analyze table names delete statistics;

Table analyzed.

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)

As I mentioned before, I got the execution plan I wanted by deleting the statistics in this case. However, this is obviously going to have dire consequences to other execution plans on this table. So ...

SQL> create or replace outline quick_bowie for category new_test on   2 select /*+ index(names uppername) */ * from names where upper(name) in ('A', 'BOWIE');

Outline created.

SQL> analyze table names compute statistics for all columns;

Table analyzed.

Lets put those precious statistics back in (yes I know DBMS_STATS ...)

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)

Back to my undesirable execution plan.

SQL> alter session set use_stored_outlines = new_test;

Session altered.

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

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=126 Card=2065 Bytes=
          10325)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'NAMES' (Cost=126 Card=20
          65 Bytes=10325)

   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=5)

So the stored outline has preserved the execution plan I wanted. Always a little suz about stored outlined as they kinda force Oracle to do things one way when maybe things have changed and it be better if Oracle did things a different way. However, in this particular example, they've proved quite useful as I had to fudge the stats to get the desirable result. I want to keep the desired result but not the fudged stats.

Cheers

Richard

Richard Foote wrote:
>
> 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