Maya Opperman skriver:

> > is it possible to create an index, but exclude records with values NULL
> > and 0 in the index column for the index?
> >
> > I have a log table with millions of records, but maybe only 5-10% have a
> > value in this specific field. And if I ever do a query using this value,
> > only records with values <> NULL or 0 are relevant.
>
> I have a similar problem. I have an IsActive field (containing only 2
> different values representing Yes/No), and usually only a small
> percentage of records are still active (and needed in calculations) (so,
> it is usually a good index to use, even though it has a low selectivity)
>
> I'm thinking of changing all those types of fields over to a date
> instead, and calling it DateDeactivated. I'll then be able to extract
> the few records where DateDeactivated is null to get the few remaining
> active ones.
>
> I haven't made the leap yet (there's an awful lot of tables to change),
> but I'm hoping it's going to work nicely..

This is a case where a more detailed selectivity statistic would help
the query optimizer. If the optimizer were to keep separate selectivity
values for nulls and say five segments of values, then your index would
be "fully selective". For example, the selectivity statistic could, in
principle look like this:

nulls: 0.0000 (no nulls)
'No'..'No': 0.98354
'Yes'..'Yes': 0.01646

For a nullable varchar field with an upper(...) index it could look like
this:
nulls: 0.23283
''..'ACME': 0.34343
'ARMIES'..'FINGERS': 0.37782
'GREEN'..'MONEY': 0.87876
'NOTEWORTHY'..'TRUSTED': 0.23211
'UNIFORM'..'ZONE': 0.43445
This would require the segment "limits" to be dynamic, of course, and I
don't know how difficult that would be inside.

It would be interesting to see a comment on this from the devel team. Is
this an idea (more detailed statistics) already being discussed? What's
planned, what's not planned, why, ...?

Kjell
--
------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64


[Non-text portions of this message have been removed]