Index usage

Hello,

I am relatively new to query tuning in Oracle and I was wondering if the following is possible in Oracle?

I have a table A. 3 fields in the table

field1 varchar2(2)
field2 varchar2(4)
field3 varchar2(6)

are indexed, so there are 3 non-unique indexes built on the A table. The total rows is several million.
Now is it possible to only use these indexes and search any combination of the 3 fields? Seems to be even when specifying the indexes using the "index" hint and rule opt. mode results are not too fast. The only way I could reach results fast is building concatenated indexes. However then I am only able to search on the leading part of the index, and also it takes up lots of storage/added overhead to keep all the created indexes on the wanted permutations of the fields updated.

I used FoxPro earlier and it used a quite efficient technology called Rushmore to help these situations. Is there no equivalent in Oracle to this? As I can recall what happened there was the engine built bitmap vectors corresponding to the record numbers (could be rowid in Oracle) and they only had to be AND'ed, OR'ed etc together to get the desired results (which had to be sorted later as the results came unsorted, but even then it worked astoundingly fast)
Wondering if it is possible in Oracle at all? The only reference that seemed to do with it a little was the mentioning of a hint that performed logical AND/OR on _bitmap_ indexes only, however my fields may not be best candidates for bitmap indexes... I was a little puzzled that I have to create so many indexes like in old times....
Thanks a lot for any pointers on this...

Csaba

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 10 2000 - 11:41:58 CST