RE: Secondary Keys

["Shaw, David" <dshaw@xxxxxxxxxxx>]

Ken,

Unfortunately I don't think it works that way.  When you specify a join
logical file to the query engine, it actually looks at the underlying
physicals to determine whether it needs to build the temporary file or not.
If the sort fields come from more than one physical member, it builds the
temporary file, essentially using the same logic that CPYF uses to copy
records from a join logical when CRTFILE(*YES) is specified.  Years ago I
had occasion to test the effect of using a join logical versus creating the
join dynamically with OPNQRYF, and could never find any difference in
performance or in what the engine appeared to do, except in those cases
where building the join actually created additional index(es) rather than
merely sharing existing ones.  I haven't noticed any change in that behavior
since then, nor have I seen any documentation changes to suggest that it's
changed.  It would be cool if it has, but I don't think that's the case.

Dave Shaw
Spartan International, Inc.
Spartanburg, SC
To subscribe to the MAPICS-L mailing list send email to
MAPICS-L-SUB@midrange.com or go to www.midrange.com and follow the
instructions.

-----Original Message-----
From: Sims, Ken [mailto:KSIMS@SOUTHERNWINE.com]

Hi Dave -

>If there are key fields from BOTH physicals in the sort, I don't 
>believe the engine can build an index without a temporary file.

That's correct.  That's why I said to run the OPNQRYF over the join file.
That way all of the key fields are in one file (so far as the OPNQRYF is
concerned) and the temporary file should not be needed.

Ken
Southern Wine and Spirits of Nevada, Inc.
Opinions expressed are my own and do not necessarily represent the views of
my employer or anyone in their right mind.
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---