To determine the join order of tables, the OpenAccess SDK SQL engine selects a join algorithm in the following list. To select a join algorithm, it evaluates the set of criteria required for each algorithm in the order shown. If the criteria is not met, the OpenAccess SDK SQL engine evaluates the criteria of the next algorithm in the list until conditions are fulfilled and an algorithm is selected.
1. Outer joins: Check for outer join and process the table in the order specified in the query.
2. Join hint: Check if the query contains JOIN HINT and process the tables in the order specified by the hint. For more information, see Join hints.
3. Join in query order: Check whether the ServiceSQLJoinInQueryOrder service attribute is set. If ServiceSQLJoinInQueryOrder is specified as 1, joins are processed left-to-right. If the ServiceSQLJoinInQueryOrder is set to 2, joins are processed right-to-left. For more information, see JoinInQueryOrder option.
4. Cost based join order: Check if all the following conditions are met:
• The query cannot reference a derived table or view. For example, the query cannot reference a table using an alias.
• The query must contain at least one join condition using the = operator.
• All tables referenced in the query must have table statistics defined for them.
5. Search condition order: Check if tables can be ordered using the search conditions. This step is skipped if SkipSearchConditionOrdering is TRUE.
The search conditions are checked to identify tables that have an explicit search condition for an index column. If an indexed condition is not found, the ordering cannot be done and the OpenAccess SDK SQL engine continues to the next algorithm. The table with the best search condition is selected as the first table to be processed. After, the OpenAccess SDK SQL engine recursively selects tables that have a relationship condition (relations are checked using join conditions in the query) with the tables that have been selected for processing. If CheckIndexOnJoinCond is TRUE, inner tables are selected only if there is an index on the join column of the inner table.
If the order for all tables cannot be determined, OpenAccess SDK resets the ordering of all tables and continues to the next algorithm.
6. Fkey order: Check if tables can be ordered using the foreign key-to-primary key relationship among tables. This step is skipped if SkipFkeyOrdering is TRUE.
Normally, the PRIMARY KEY table is processed first, and then the FOREIGN KEY tables. When CheckIndexOnJoinCond is TRUE, the PRIMARY KEY table is processed first only if an index is found on FOREIGN KEY table. Otherwise, the PRIMARY KEY table is checked for an index and the FOREIGN KEY table is processed first.
If conflicting foreign key entries are found, where one entry indicates that Table X is to be processed before Table Y and the other indicates the opposite, the ordering cannot be done using the foreign key information and the OpenAccess SDK SQL engine continues to the next algorithm.
7. Join condition order: Check if tables can be ordered based on join conditions in the query. Join conditions are checked, and the table with indexing on a join column is processed as an inner table. If both tables have an index on join columns, the table with the best index is processed as the outer table and the other table is processed as the inner table. If a table has no join conditions it is processed at the end.
Note: The table with the best index is the one that encompasses the most columns in the query and where the index is marked as unique.
If the query has no join conditions or join conditions on index columns, it is processed in the order specified by the query.
