Re: SQL sort

On 03-Sep-2015 14:34 -0600, CRPence wrote:

On 03-Sep-2015 14:14 -0600, Booth Martin wrote:

I am fairly new with SQL so please excuse what are most likely very
simple questions.

I am looking to understand the pros and cons with SQL. On large
files with data that will be used in various applications and in
various sort-by schemes, are logical files a help?

Keyed logical files [for which a Keyed Access Path is created] can
be helpful, but mostly they are helpful for selection [WHERE clause
predicates] and joins [JOIN predicates] more so than for ordering
[ORDER BY], because often actual /sorting/ is more efficient than
utilizing the key [per /random/ I\O] because a subset of selected
row data often can be accessed as /pages/ of effectively contiguous
memory.

If so, does one reference the physical file and then SQL finds the
best logical, or does it help to specify a specific logical file?

The SQL is effectively restricted to operating on the physical and
using optimization for choosing a compatible keyed and\or selective
Access Path; i.e. the Logical Files with the SQL attribute are
disallowed for a table-reference, and depending on what level of SQL
support, only the Classic Query Engine (CQE) knows what to do with a
table-reference that includes a DDS LF. <<SNIP>>

That should have said "the non-VIEW Logical Files with the SQL attribute are disallowed for a table-reference" because the VIEW Logical File is already composed as SQL, thus the SQL need not /translate/ the LF into SQL like effectively must transpire for a table-reference that names a DDS LF. In the past, only the CQE could support a DDS-LF for a table-reference, but over time [and newer releases] there has been added support for the understanding by the SQE of the DDS-LF both in optimization and implementation of the SQE query and more recently to include the explicit table-reference; i.e. the query is not immediately dismissed to the domain of CQE, and may /translate/ the DDS-LF into the equivalent SQL, essentially treating the DDS-LF as if that table-reference were an SQL VIEW LF.