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

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


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

This mailing list archive is Copyright 1997-2022 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.