Re: Sql Table / View / index issue

The CQE [Classic Query Engine] has always allowed specification of the LF to select from, and in so doing, honoring the mapping and selection defined in the DDS logical file. The CQE also has some capabilities to correlate the derived keys [and select/omit I believe] specified in the keyed DDS logical file with the query selection and ordering; i.e. the CQE has always had the capacity to utilize some keyed DDS LF in both its optimization & implementation phases. It is the SQE {SQL Query Engine] that did not, still does not, and presumably will never, have any support for using the DDS derived keys for its implementation of the SELECT; I am not sure if\what statistics non-SQL access paths might provide for the SQE.

As was mentioned by others, the manner to ensure /fast SQL access/ is to create INDEX objects that provide statistics and\or the potential for a more selective path to the data. With various INDEX objects over the columns involved in the query for its data selection [the WHERE clause, and possibly for ordering], the optimizer can make the decisions which will hopefully find one of the fastest paths to the data to be selected [and ordered].

Regards, Chuck

Lim Hock-Chai wrote:

Uuummm, Chuck only said that SQL DDL, in V6R1, allows where clause when create index file. Does that also mean that SQL engine in
V6R1 will know to use the access path of index with where clause?
Does SQL engine also know to use access path of LF with select/omit
in V6R1?

"Lim Hock-Chai" wrote:

Our OS in not at v6r1 yet. But the main reason I would need Index Over View is to allow fast SQL access to records in file that meet certain criteria. As you know, SQL engine does not know to use LF access path if the LF contains select/omit. I guess, from your reply, my issue with this is resolved in V6R1.