When joining more than two tables, you cannot control the order tables are joined, and only two at a time will be joined at a time. So, there is a high probability that temp indeces will be used.

>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?