Problem: If using v4.21 or lower the InterBase optimizer does not use indexes properly for MIN and MAX. This was a bug and has been fixed in v5.x Solution: D:temp>isql emp_421.gdb Database: emp_421.gdb SQL> show version; ISQL Version: WI-V4.2.1.328 InterBase/x86/Windows NT (access method), version "WI-V4.2.1.328" on disk structure version 8.0 SQL> show index employee; NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME) RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO) RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO) <--- primary key TEST_OPT_IDX DESCENDING INDEX ON EMPLOYEE(EMP_NO) <--- user defined SQL> set plan; SQL> select min(emp_no) from employee; PLAN (EMPLOYEE NATURAL) <---------- Bad !! MIN ====== 2 SQL> select max(emp_no) from employee; PLAN (EMPLOYEE NATURAL) <---------- Bad !! MAX ====== 145 --------------------------------------------------- D:temp>isql emp_511.gdb Database: emp_511.gdb SQL> show version; ISQL Version: WI-V5.1.1.680 InterBase/x86/Windows NT (access method), version "WI-V5.1.1.680" on disk structure version 9.0 SQL> show index employee; NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME) RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO) RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY) RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO) <--- primary key TEST_OPT_IDX DESCENDING INDEX ON EMPLOYEE(EMP_NO) <--- user defined SQL> set plan; SQL> select min(emp_no) from employee; PLAN (EMPLOYEE ORDER RDB$PRIMARY7) <--- Fixed the optimizer !! MIN ====== 2 SQL> select max(emp_no) from employee; PLAN (EMPLOYEE ORDER TEST_OPT_IDX) <--- Fixed the optimizer !! MAX ====== 145
Last Modified: 26-OCT-00