Inability to use function-based indexes at times

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Mon, 26 Aug 2002 16:05:41 -0600
Message-ID: <3D6AA635.9F5E6A21@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 - 17:05:41 CDT