Icon View Incident Report

Serious Serious
Reported By: Jeff Dunlop
Reported On: 4/27/2010
For: Version 2.03 Build 12
# 3198 Tables or Derived Tables That Contain 0 Rows Confusing Query Join Optimizer

This query is taking a very long time, and it used to be very fast. The claim table doesn’t have a 1012 in it so it should just be an inner join with claim1 as the driver, but the planner says that script as the driver is the most optimal join.

SELECT   Script.*
FROM "Script"  
   INNER JOIN (SELECT * FROM Claim WHERE ClaimPeriodNumber = 1012) Claim1
      ON Script.ClaimId = Claim1.ClaimId

Comments Comments
The derived table was returning 0 rows, which was matching on several thousand rows in the Script table with a Claim column containing a NULL value. This fact confused the join optimizer and made it think that it was more efficient to keep the join ordering as-is. The optimizer now correctly does not compute join costs for tables or derived tables with zero rows in them.

Resolution Resolution
Fixed Problem on 4/27/2010 in version 2.03 build 13

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