Index Optimized Retrieval

In this section:

Optimized Joins

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 your platform.

Optimized Joins

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 Phase 3.
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.