In many cases I have found that using multiple queries works well and it could be worth a try.
>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 index>
>The 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_Uniq>SELECT 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 index>
>No 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?