Re: SQL index possible with keys from two tables?

Douglas Handy wrote:

Last I knew, this wasn't possible but wanted to make sure I didn't
miss something.

A join LF in DDS only supports key fields from a single file. Is it possible yet in SQL at V5R4 to create an index with keys from two
tables? I suspect no, because running under debug the optimizer
messages aren't recommending an index I don't have. But I wanted to
make sure...

The JLF does create indexes over the secondary file(s), to implement the join and any record selection. The join is always dynamic, but the record selection can be either static or dynamic. The limitation is merely that the key for /ordering/ can not be included in that JLF definition, if it would span beyond the primary file for the join. There is actually no reason to have such a key in SQL. The concept for such a key makes sense only in a RLA view of the database, because SQL uses the ORDER BY instead of the file definition itself, to establish collation.

The database does not have the capability for an index to maintain both the selection and _ordering_ across different files in a join; intuitively for the generic join, since the join is done dynamically. Since a join may generate records, such an index would have to maintain multiple rows that do not even really exist, except in the join result. In general it is best just to do the join dynamically. The exceptions are the simplest inner join and where temporary copies of small amounts of data make sense. For small amounts of data it is simple enough to just make those temporary copies in the application just prior to the query of the data. For the simple inner join, optimized dynamic joins with proper indexes for static SQL [the RLA is similarly static] will likely be able to perform extremely well, often significantly faster than a JLF, plus with no added overhead for maintaining a very limited function & newfangled index.

For the SQL, just create index objects that support the queries, and enable the SQE; i.e. do not do anything that requires the queries be forced into the CQE. Most likely, since the apparent mindset is RLA [inferred from the desire to effect such a key], the indexes are not actually very conducive for use by SQL.

Regards, Chuck