Matt Clark wrote:

>
> In a recent effort to prune redundant data I removed 18 million rows
> from a 20 million row table. There are 5 indexes on the table ...
> ... several programs that indirectly (via triggers) add rows to
> this table refused to work and secondly I had a garbage collection
> session running for nearly 4 hours before I was forced to kill it ...
> After finally resorting to dropping the indexes and the table
> the garbage collection seemed able to complete.
>

The answer is slightly different for different versions of Firebird.
You don't say what version you're running. If it's old, then
you've also probably run into the inefficiency of garbage collecting
indexes with lots of duplicates.

>>From what I've read subsequently I'm guessing this problem would have
> been avoided if I'd disabled the indexes before the delete

Yes.

> but I'm now
> curious to understand why this created such a problem for the garbage
> collection? Would it ever have completed or did I create a condition
> that the GC couldn't fix and why did dropping the table ultimately fix
> it?

It would probably have completed sometime. You didn't need to drop
the table - just dropping the indexes would have been sufficient.
The garbage collection was trying to clean up 90 million index entries.
However, if you can drop the table (meaning there aren't procedures
and views nested six levels deep that depend on it), you'll find
that copying the 2 million records you want to a temporary table,
dropping the primary table, then recreating it, reloading the data,
then recreating the indexes is much faster than deleting 18 million
rows.


Good luck,

Ann