Hello

I have another solution for such cases
for these tables where primary key comes from generator I create
additional integer column that gets value from primary key column (in
before insert trigger). Then record can be considered as 'active' when
'active_status' column has positive value and inactive when negative
value. The selectivity of additional index created on 'active_status'
column is as good as primary key.
For tables without integer primary key I create dedicated generator to
'feed' additional column in before insert trigger.

I use this solution whenever I have to use 'status' of certain record.
Positive values of this columns mean that the processing of this record
is in progress on certain stage. Negative values mean that processing
has been completed. So only relatively small amount of records will have
positive status value in certain moment, most of them will have negative
values. Selectivity comparable with primary key. Works perfect for me
for years...

If more details are needed, I'm willing to help...

HTH

Marcin

W dniu 14.06.2011 09:09, Maya Opperman pisze:

>> 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..
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>