Problem solved: Inability to use function-based indexes at times

I am happy to report that with a bit more study and thought to my problem, I was able to figure out where I had gone wrong.

I failed to mention in my previous post that I was using a plain ol' domain index on the mod() function. This worked quite acceptably when choosing just one 10% chunk of data. However, since I was interested in being able to choose not only a single 10% chunk but also several (these correspond to 20%, 30%, etc.), I discovered that a bitmap index would work better for me.

This paragraph in the "Oracle8i Designing and Tuning for Performance, Release 2 (8.1.6)" sealed it for me:

Bitmap:
This uses a bitmap for key values and a mapping function that converts each bit position to a rowid. Bitmaps can efficiently merge indexes that

correspond to several conditions in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

After I realized that a where clause like "mod(id,10) < 7" (corresponding
to roughly 70% of the rows) was equivalent to bunches of "or's", I decided that a bitmap index would perform much better for me. It did, and now I can choose any roughly random percentage of rows in no more than two seconds. Hurray!

Tom Received on Mon Aug 26 2002 - 18:45:56 CDT