| This problem has been entered into bug. It's bug #450934.
I managed to reproduce this problem with a test database. I took the
partial database provided with the bug and used RMU/EXTRACT to create a
new database with the same definitions (of course the storage areas
were minimized). Then I generated SQL scripts to update both the table
and index cardinalities to be the same as the customer's database. This
was not enough to reproduce the problem.
From a review of the source code, I found that the divide by zero error
was occurring during the cost estimation phase of the query
optimization. I then deleted all the rows in RDB$WORKLOAD pertaining
to the trouble table in the test database and re-inserted those rows
from the customer's database. The rows in RDB$WORKLOAD for the
customer's database contained non-zero values for RDB$DUPLICITY_FACTOR.
Duplicating those values in the test database was enough to reproduce
the problem.
Since RDB$DUPLICITY_FACTOR has a default value of zero, and is only
initialized if RMU/COLLECT is run, I figure it is probably OK to set
RDB$DUPLICITY_FACTOR to zero for this table as a workaround while we
try to correct the problem. This can be done with the following query:
UPDATE RDB$WORKLOAD
SET RDB$DUPLICITY_FACTOR = 0.0
WHERE RDB$RELATION_ID = (SELECT RDB$RELATION_ID
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME = 'problem_table');
Steve.
|