community.borland.com

Article #25549: Does InterBase use indexes with MIN and MAX

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