Thank you Chuck, I remember that 7.1 introduced some extra functionions for the views, and I could not find the way to do the index over the join. Now I know that it is not possible yet.
The MQT is a good option, but I will probably put a more complex SQL statement in my program to get the data directly. The tables are not to big, so I should not have performance problems.
Eventualy I can hide that in a service program.
______________________________________________________________________________________
CRPence wrote:

On 11 May 2012 07:31, Raul A. Jager W. wrote:

Using DDS I can create a "view" that has an index and some data from
one table and data from other joined tables.


The DDS Join Logical File (JLF) can include K-specs to define a keyed access path on the /primary/ [for DDS, the first] file in the join. The database implicitly creates the access path(s) for the joined-to file. The join is dynamic when the file is opened, just as with a query. So effectively the keyed JLF is a VIEW with an ORDER BY [but with a static sort sequence] that could be applied to the columns of any one TABLE that could be the join-primary.


Can I do the same using SQL? at V7.1


There is no ORDER BY in the fullselect of a VIEW. The SQL VIEW merely defines the result-set, not the order. Ordering is always dynamic for the environment\result-set. There is no CREATE INDEX on a VIEW because an INDEX is [considered to be] on the data. The extension of the CREATE INDEX to allow selection [WHERE clause] and some limited column\expression does not include the capability to define the JOIN indexes [no JOIN clause] as can be effected by the DDS JLF. Because the JOIN is dynamic, various CREATE INDEX against the TABLE data can enable the optimizer to use a\some of those existing indexes [access paths] to access the data, possibly instead of creating access paths dynamically at run-time. The run-time is the SELECT query against the VIEW. Specify an ORDER BY on a SELECT from that VIEW, and the collation reflects the Sort Sequence (SRTSEQ) that is defined for the statement by the executable or the job.

If the VIEW is defined and a program would prefer the same effect as the JLF for that SQL VIEW, then the OPNQRYF can generate a shared ODP with ordering using the KEYFLD() and SRTSEQ() parameters. Some restrictions may apply [because of or\]and the query may be forced to run CQE. Of course just defining and using the JLF might be easiest.

If making a copy of the joined data is acceptable, a MQT or some other copy of the join result set could be created [and maintained], over which an INDEX can be created. And because the data is since, in just one TABLE, the INDEX can be created over the data from [what was prior, in] both TABLEs.

Regards, Chuck


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].