Denis,

Since you likely are running with SET DELETED ON it is probable that partial is related to the absence of a TAG on DELETED(). But this is not bad.
Your ORDER command confirms that you do have a TAG on gecode and I think that it is reasonable to assume that the 'partial' result proves that the TAG *is* being used.
You can check if it is the absent TAG on DELETED by repeating the query with a SET DELETED OFF prior to running it. You should get FULL as the optimization if that is the problem. but. . . you will most likely be far better off with only partial, unless you have a significant number if DELETED() records in the table!!!
*IF* that is the case, then a TAG on DELETED() will give you FULL and it will be better, BUT if you have no/few deleted records in the table then accpet the partial is best for this query.
IN ANY CASE, if you did temporarily SET DELETED OFF, do not forget to remove it!!

In the second example the COLLECTIONS table and the MYCODES table need to have a TAG on SPNUMBER. In this case you should again get PARTIAL if there is no TAG on DELETED(). Same rules as above apply here as regards DELETED() TAG.

cheers

>I am having trouble getting my head around SQL rushmore optimiztion. Using the sys(3054) function, I get excited if I can score Partial optimization. Often, none though. So, I guess my SQL and/or indexing is up the creek. In this example, the first SELECT gives partial optimization, the second none.
>
>* assemble a list of species codes from SP
>SELECT SP
>SET ORDER TO GECODE
>SELECT spnumber FROM species into CURSOR mycodes WHERE gecode = gecodex
>
>* use species codes to query the required records from HS
>SELECT HS
>SET ORDER TO SPNUMBER
>SELECT * FROM COLLECTIONS INTO TABLE (tempx) WHERE SPNUMBER IN (SELECT SPNUMBER FROM mycodes)
>
>As far as I can see, the indexes are OK. Is a better or 'correct' way to do this. THANKS.