We have the following query:
SELECT Custname, wono, pno.part_no, pno.revision, to_dept_id, CountToDate, shipdate;
FROM crspartno pno;
INNER JOIN inventor ON pno.uniq_key = inventor.int_uniq;
INNER JOIN crsCustsSel cust ON inventor.custno = cust.custno;
ORDER BY custname, pno.part_no, to_dept_id;
INTO CURSOR crsmovedfinal READWRITE
which runs very slowly. The query plan shows
Rushmore optimization level for intermediate result: none
Rushmore optimization level for table inventor: none
Rushmore optimization level for intermediate result: none
Joining intermediate result and table inventor using temp index
Joining intermediate result and intermediate result using temp indexThe problem is that the inventor table has an index on int_uniq but Rushmore doesn't seem to recognize it. When the query get split into two queries the optimization level improves dramatically
SELECT Custno, wono, pno.part_no, pno.revision, to_dept_id, CountToDate, shipdate;
FROM crspartno pno;
INNER JOIN inventor ON pno.uniq_key = inventor.int_uniq;
INTO CURSOR crsmovedfinal READWRITE
Rushmore optimization level for intermediate result: none
Rushmore optimization level for table inventor: none
Joining intermediate result and table inventor using index tag Int_UniqSELECT Custname, wono, part_no, revision, to_dept_id, CountToDate, shipdate;
FROM crsmovedfinal mv;
INNER JOIN crsCustsSel cust ON mv.custno = cust.custno;
ORDER BY custname, part_no, to_dept_id;
INTO CURSOR crsmovedfinal READWRITE
Rushmore optimization level for intermediate result: none
Rushmore optimization level for intermediate result: none
Joining intermediate result and intermediate result using temp indexNo matter how I re-arranged the pieces of the query, I still couldn't get the "proper" tag to be used.
I've never found a construction such as
INNER JOIN myTable ON condition USING INDEX TAGS
but is there some way to structure the query so that Rushmore will do a better job selecting which tags can be used?