Re: Inability to use function-based indexes at times

Richard -

Thanks very much for your thoughtful response. As you can see earlier in this thread, I was able to get my queries of data quantities more than
10% to perform just fine after I switched to a bitmap index from a domain index. In my first test (I chose just 10% of the data, corresponding to mod(id,10) = a single value), the query performance with a domain index was outstanding. I was hoping that queries of more than a single value of mod(id,10) would be nearly as fast because the same work along with some sorting would be done just a few times more.

In any case, I'm pleased that the query is performing well now. I'm tempted to write to Tom Kyte, Chris Beck, HJR, or Jonathan and maybe suggest my silly test case as an example of how funky the CBO can be when it comes to indexes.

Tom

Richard Foote wrote:
>
> Hi Thomas,
>
> My suspicion is that the optimizer is actually getting it right here.
>
> Assuming an even distribution of numbers, then mod(x,10) < 2 is going to
> return approximately 20% of all data (note maths along with spelling,
> history, geography, physics, chemistry, biology and sex studies are all
> particularly weak areas in my education)
>
> If this is correctish, then a conventional scan index would not be
> appropriate as it would be far too expensive. Note depending on your version
> of Oracle and how you have computed statistics, valid hints are ignored if
> the optimizer really really thinks you are wrong with your hint (if you
> listen carefully, you can even hear it mutter things such as "mad, been mad
> for bloody years...").
>
> If you are determined to use the index, you can try using histograms (but if
> the data is evenly distributed then there would be zip benefit) or try
> deleting your stats, get the optimizer to adopt your hint, store the plan in
> a stored outline and use the store outline for future executions.
>
> Good Luck
>
> Richard
> "Thomas Gaines" <Thomas.Gaines_at_noaa.gov> wrote in message
> news:3D6AA635.9F5E6A21_at_noaa.gov...
> Folks -
> I've got a certain query that I'd like to speed up, but I'm at a loss
> right now about how to do it.
> I have a rather sizeable table (1.6 M rows) with a primary key
> on the single column "id." My group will be displaying these points
> on a map, and 1.6 M points is too many for the poor display. When
> zoomed out completely, we want to display just 10% of the points,
> and display more as we zoom in more. When mapping just a small
> region, we can easily display every point in that region.
> To get a representative sample of points, we use the mod() function
> similar to this:
> select count(*) from gravity.grav_31 where mod(id,10) < 1;
> Elapsed: 00:00:00.09
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=354 Card=1 Bytes=5)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (RANGE SCAN) OF 'GRAV_31_MODID'
> (NON-UNIQUE) (Cost=354 Card=18150 Bytes=907500)
> This works great because the id column is a straight numerical sequence
> with no gaps and the data is evenly distributed. It uses the function-based
> index "GRAV_31_MODID" built on mod(id,10) with no problems.
> However, when I simulate zooming in a bit, the function-based index
> isn't used any more:
> select count(*) from gravity.grav_31 where mod(id,10) < 2;
> Elapsed: 00:00:08.60
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=532 Card=1 Bytes=5)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'SYS_C006718'
> (UNIQUE) (Cost=532 Card=363000 Bytes=1815000)
> I'm not sure how to handle this. Do you think that it would help to remove
> the primary
> key on the "id" column? I can probably live without it.
> Even when explicitly specifying the index "GRAV_31_MODID" using an index
> hint, it's
> still ignored!
> Do you have a suggestion?
> Thanks very much,
> Tom
Received on Tue Aug 27 2002 - 00:17:16 CDT