Icon View Incident Report

Minor Minor
Reported By: Helmut Schiefer
Reported On: 12/8/2004
For: Version 4.15 Build 1
# 1912 Optimizer Chooses Wrong Plan for Optimized Condition Joined with Same with Zero Candidate Rows

Sometimes the optimizer does not work correctly. I have compared the version 3.30 and 4.15 with DBbench and the result was that the version 4.15 is about ~20% slower than version 3.30. The problem was only Query3 from DBbench. This query take much more time as in version 3.30

/* Table1 from DBBench from Ole */ 
CREATE TABLE IF NOT EXISTS "Table1"
(
   "T1_Key" INTEGER,
   "T1_SFld1" CHAR(30),
   "T1_SFld2" CHAR(30),
   "T1_Fld1" CHAR(10),
   "T1_Fld2" CHAR(10),
PRIMARY KEY ("T1_Key") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);
CREATE INDEX IF NOT EXISTS "ix_T1_SFld1" ON "Table1" ("T1_SFld1");

Slow Query: (un-optimized)
============
SELECT *
FROM TABLE1
WHERE(T1_SFLD1 like 'Smith')

Fast Query:
============
SELECT *
FROM TABLE1
WHERE(T1_SFLD1 = 'Smith')

Slow Query:
is PARTIALLY-OPTIMIZED, costs 0 bytes
============
SELECT *
FROM TABLE1
WHERE
  T1_KEY BETWEEN 3800 AND 4000
  AND (T1_SFLD1 = 'Smith')
============
is PARTIALLY-OPTIMIZED, costs 0 bytes

Fast Query:
============
SELECT *
FROM TABLE1
WHERE
  T1_KEY BETWEEN 3800 AND 4000
  AND (T1_SFLD1 like 'Smith')
============
is PARTIALLY-OPTIMIZED, costs 500000 bytes



Comments Comments and Workarounds
The DBISAM optimizer was getting slightly confused when both conditions joined by an AND operator were optimized, but the right condition resulted in 0 rows, while the other condition was resulted in more. DBISAM would correctly determine that the left condition with more candidate rows should be evaluated as a record scan based upon the results of the right optimized condition, but did not flip around the conditions properly. The workaround is to flip around the conditions so that the condition returning zero rows is evaluated first.


Resolution Resolution
Fixed Problem on 12/9/2004 in version 4.16 build 1
Image