Re: Function Based Indexes with in clause?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 9 Aug 2002 10:27:06 -0500
Message-ID: <uwur0t5ne.fsf@grossprofit.com>

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

Received on Fri Aug 09 2002 - 10:27:06 CDT