At 07:42 PM 3/31/2002 +0000, csswa wrote:

>Yes, I just checked the selectivity of
>my test-generated data and it is very low for the three main fields.
>Only 146 distinct NAME fields, 106 for PHONE, and 69 for COUNTRY.
>There are single-field indexes on NAME and COUNTRY in addition to
>integer PK index. Across a million-record db that has to be costly
>in some way.
>
>but I don't get how indexes factor into sweeping?? We are just
>talking about time to locate each record??

No, sweeping and garbage collect eliminate both the record
and references to it in indexes. It finds the records by
walking through the tables sequentially (natural order).

To remove entries from indexes, the code gets the value from
the record version it's removing, checks that the values isn't
used in a version of the record that's staying (reasonably cheap,
unless you do something like modifying the same record 500
while some transaction is open). If the value no longer applies
to the record, Firebird/InterBase goes to the index and looks for
the value paired with the record id. Only values propagate up in
the index, so it must examine all entries in the index with that
value until it finds the one with the matching record id. Here's
the nasty bit. Duplicates are stored at the front of the chain,
so the oldest records are last. Walking a table in natural order
removes the oldest records first.




Regards,

Ann
www.ibphoenix.com
We have answers.