Re: Inability to use function-based indexes at times

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 27 Aug 2002 10:13:21 +1000
Message-ID: <fmza9.15808$g9.49525@newsfeeds.bigpond.com>

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 Mon Aug 26 2002 - 19:13:21 CDT