Index Optimized Retrieval
The SQL Translator improves query performance by generating
optimized code that enables the underlying retrieval engine to access
the selected records directly, without scanning all segment instances.
For more information about index optimization
and optimized join statements, see your Server documentation for
The SQL Translator accepts joins in SQL syntax. SQL
language joins have no implied direction. The concepts of host and
cross-referenced files do not exist in SQL.
The SQL Translator analyzes each join
to identify efficient implementation. First, it assigns costs to
the candidate joins in the query:
- Cost = 1 for
an equijoin to a field that can participate as a cross-referenced
field according to FOCUS join rules. This is common in queries against
relational tables with equijoin predicates in the WHERE clause.
- Cost = 16 for
an equijoin to a field that cannot participate as a cross-referenced
field according to FOCUS join rules.
- Cost = 256
for a non-equijoin or an unrestricted Cartesian product.
The Translator then uses these costs
to build a join structure for the query. The order of the tables
in the FROM clause of the query influences the first two phases
of the join analysis:
- If there are
cost=1 joins from the first table referenced in the FROM clause
to the second, from the second table to the third, and so on, the
Translator joins the tables in the order specified in the query.
If not, it goes on to Phase 2.
- If Phase 1
fails to generate an acceptable join structure, the Translator attempts
to generate a join structure without joining any table to a table
that precedes it in the FROM clause. Therefore, this phase always
makes the first table referenced in the query the host table. If
there is no cost=1 join between two tables, or if using one requires changing
the table order, the Translator abandons Phase 2 and implements
- The Translator
generates the join structure from the lowest-cost joins first, and
then from the more expensive joins as necessary. This sorting process
may change the order in which tables are joined. The efficiency
of the join that this procedure generates depends on the relative
sizes of the tables being joined.
If the analysis results in joining to a table that cannot participate
as a cross-referenced file according to FOCUS rules (because it
lacks an index, for example), the Translator generates code to build
an indexed HOLD file, and implements the join with this file. However,
the HOLD file does not participate in the analysis of join order.