Re: Index statistics causing query performance problems

Well without example SQL and lacking indexes how can we help? We can blind guess that your indexes are badly designed.

B-tree type indexes work best when the attribute with is well distributed over a large domain (lots of values, not many repeats). If it is a multi-attribute index the "best" attribute should be first in the index definition.

Bitmap indexes work best when there is a small domain, skewed distribution and you are looking for a value that is rare. (few values like yes,no, maybe with one million yes, one million no and 5 maybe, look for maybe).

At http://www.sofbot.com/ I have an index screening script, check_index, to screen a schema for crippled b-tree first attributes. I will publish another script soon that gives attribute order, number of values for an index.

There are also star schema hints, if what you are doing is datawarehouse stuff.

-- 


---------------------------------------------------------

Steven Hauser 
email: hause011@tc.umn.edu   URL: http://www.tc.umn.edu/~hause011


---------------------------------------------------------