Icon View Incident Report

Serious Serious
Reported By: Jeff Dunlop
Reported On: 12/7/2009
For: Version 2.03 Build 6
# 3115 Index Statistics Being Calculated Improperly for Tables and Causing Query Optimizer to Make Mistakes

The following is an example query that demonstrates the execution planner making a poor decision. No customer records exist in the customer index, but it still takes 0.5 seconds to run.

select salecode from sale
where customercode > 148984
and saletype = '4'

Comments Comments
The statistics for the indexes were being calculated improperly for inner nodes of the indexes, thus with very large tables these statistics were way off, causing the query optimizer to make incorrect decisions about how to order the WHERE clause expressions.

There is a new clause for the REPAIR TABLE statement called STATISTICS that can be used to correct this issue with any table. The following is an example of repairing such statistics for the example table:


Resolution Resolution
Fixed Problem on 12/9/2009 in version 2.03 build 7

Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial