Kjell,

> I have a table "LargeTable" with ~170 million records.

That's a "reasonable" sized table! ;-)

> I need to update a single column on a "small" subset (maybe a million records or so),
> but the subset selection requires references to other tables.

A difficult problem.

How many relates tables are used?

Would the related tables be first level or second level joins?

How many entries are there in the tables?

Are the references FK values?

How many total unique FKs values would the criteria resolve to?

Do you have indexes on the update table for the FKs?

> Unfortunately, Firebird does not support joins in update or delete queries,
> resulting in natural scan of "LargeTable" and consequently 170 million
> executions of each correlated subquery (required to form my desired
> selection).
>
> So, is there a better way?

Depends on how you define "better".

At the moment, the best scenario I can see is that you still have a natural scan. But that the "cost" of the related tables references would be minimized.

> Note: I tried a "straightforward" update with correlated subqueries. It took 17
> hours. I think a natural scan takes about an hour...

I would hope that the new approach would takes 2X natural scan.

How would that performance be?


Sean