On 16/12/2010 15:39, Kjell Rilbe wrote:
>> 5. The table has several indices, on one column each, one of which is a
> PK. There are 18 columns, 9 of which have an index. 7 of the indices
> have selectivit < 0.0006, one has 0.5 and one has selectivit 1. The 0.5
> index will become more selective later on while the 1.0 one will
> probably keep having over 99% nulls, but be very useful for the records
> where the indexed column is not null.

I'm wandering if in such a case, theorically, it's not better to have
two (or more) tables and then SELECTing from a VIEW with a UNION ALL of
them when absolutely there is a need of other types of filtering.

I mean: a table with a million row with the same NULL value in a field
(say FLD_X) that has low chances to change his value and a trigger in
the view can delete from a table and insert in the other when the value
changes: as an example this sould be like a status field, that show
progress in a particular phase of work. Often searches are for pieces in
a specific phase and then you can select directly from the real table
and not from the generic view.

What are the lòimits in a solution like that (I suppose a field that
changes very very rarely and with low risk of conflicts)?

Ciao.
Mimmo.