Re: multi-column indexes, statistics and selectivity
From: bugbear <bugbear_at_trim_papermule.co.uk_trim>
Date: Mon, 07 Dec 2009 14:54:23 +0000
Message-ID: <UKmdnV0d9O-CioDWnZ2dnUVZ7tFi4p2d_at_brightview.co.uk>
Robert Klemme wrote:
> Are there cases where it makes sense to use a FBI to get "multi column"
> histogram information? Of course, queries then would also have to use
> that concatenated value as query criteria which makes usage of this
> quite nasty (especially if the SQL is generated by some kind of
> persistence container).
Date: Mon, 07 Dec 2009 14:54:23 +0000
Message-ID: <UKmdnV0d9O-CioDWnZ2dnUVZ7tFi4p2d_at_brightview.co.uk>
Robert Klemme wrote:
> Are there cases where it makes sense to use a FBI to get "multi column"
> histogram information? Of course, queries then would also have to use
> that concatenated value as query criteria which makes usage of this
> quite nasty (especially if the SQL is generated by some kind of
> persistence container).
I though about this rather carefully.
It is possible to do this (in my case, at least)
However, the "accurate" statistics associated
with the FBI make it quite important
that there are not "too many" other indexes that
the CBO might consider.
Because due to the inapropriate estimation of selectivity the CBO is quite determined to use almost-anything-other that YOUR FBI.
It doesn't "know" that the apparently low
selectivity of your FBI is because it's
the only index with accurate stats!
BugBear Received on Mon Dec 07 2009 - 08:54:23 CST