×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 16 Apr 2013 11:34, Gqcy wrote:
I am reading about converting my DDS files to SQL...
Be sure if going down that path, that the decision to do so is made
to effect something specific; i.e. such a change should not be
undertaken simply "because SQL is..." and thus perhaps change just
specific files. There should be little to no reason to change more than
some specific set of files, and should be done only when there are
reasons that can be articulated; e.g. doing so, solves a problem. IMO
the better approach over conversions is to start using DDL.
Most of our Logical files are doing both, ordering and record
selection...
However I am reading that I should _only_ create indexes for my
ordering.
Effectively; the WHERE clause should generally not be included, esp.
if the applications are changing from RLA to SQL. Though FWiW, the
INDEX is not really used much for /ordering/ in SQL, as they are mostly
used for selection. Although implicitly, the concept of ordering is
apparent from a key, for the SQL the most selective key generally should
be first, which is atypical for feasibility for ordering. That is
because unlike RLA which often desires ordering by the least selective
key first, the SQL wants the most selective key for selection in order
to encounter\process as few rows as possible.
I see I and <ed: where I can> put in a where clause for my indexes,
but should I?
Atypically. When there is a specific matching and common query that
has that same selection, then there possible value in doing so. The
WHERE clause is IMO a much nicer way to specify the selection logic than
using the DDS COMP on S and O specs. A good replacement if RLA vs SQL
is continued against the data; the predicates are much easier to change
than the S\O of DDS LF.
The more important value I find in having the INDEX is when mapping
to a consistent data type for typical queries. For example if one file
has an INT type and the other a DECIMAL(8), then a casting expression to
define the key of one to match the other allows comparisons via the
keyed access path vs having to either create one dynamically by the
query or performing the conversion for comparisons at run-time.
Should I create SQL indexes just on ordering?
Effectively... unless those with selection are still needed for RLA
vs for SQL, or a commonly-run query can and does take advantage of the
INDEX with the WHERE clause either because the query has the identical
predicates [such a query could be encapsulated, in a VIEW, for example]
or the statistics from that INDEX are just helpful to the optimizer.
will the Logical files share the index, even though the logical
files have select/omit logic?
No. The WHERE clause is like an LF without DYNSLT. The selection
logic defines the rows selected for the keyed access path, and thus
there is no sharing; each file's access path is maintained separately.
And I think no sharing is the effective rule for SQL INDEX anyhow; i.e.
while a DDS LF can share an access path of an INDEX or a CONSTRAINT with
a compatible but more expansive key, IIRC an SQL INDEX can not share any
access path except an exact match to either another INDEX or to the
PRIMARY or UNIQUE KEY constraint [implicitly adopting the rules of the
constraint].?
midrange.com
