skip to main content
Designing and coding the IP : Join processing
 

Join processing

OpenAccess SDK SQL engine supports four different join modes, or ways of handling joins for efficient processing of joins of variable table sizes. The join mode that you choose depends largely on the capability of your data source. The OpenAccess SDK join modes allow variable-sized tables to be joined without consuming large amounts of memory.
The four join modes are:
• One row at a time from an outer table: This is the default mode of join processing in which an outer table is queried, and then for each row in the outer table, the inner table is queried. This results in efficient join processing on the ISAM type of data store, in which a record can be quickly accessed based on its key value.
• Block join: Inner tables are queried for a block of master rows, and during inner table processing the IP can optionally retrieve all rows of the inner table. This reduces the number of times the inner table is queried. This mode is optimal where it is expensive for the data source to process requests for a single matching key value.
• Join pushdown: The IP is expected to build a hierarchical rowset representing the join results. This mode works best for data sources that store data in hierarchical format – like an object data store, or for cases where one physical record is exposed as multiple virtual record types (tables).
• Star join: A large central table (a fact table) is joined to two or more smaller tables containing descriptions of the column values in the fact table (dimension tables). The FACT table is Foreign Key table for all the Dimension tables, which have no relationship between each other.
The order in which tables are joined can significantly affect performance. OpenAccess SDK uses an algorithm that the IP implementer can tune. The order in which the tables in a join are processed is determined by both of the following:
• Conditions in the search expression
• Relationship between the tables as specified in the OA_FKEYS table
Once the processing order is determined, the OpenAccess SDK SQL engine queries for matching records in the first table and recursively queries each additional table once for every matching record from the previous table.
Note: The inner table is not queried for every row in the outer table if the IP implements either the optional JOIN processing (see Implementing join pushdown), the optional BLOCK JOIN processing (see Implementing Block Join processing), or the optional STAR JOIN processing.
The following table shows the connection options that the IP can set, using dam_setOption(), to control how the tables are ordered. Variables defined in the "JoinAlgorithmVariable" column explain the Join algorithm.
 
Table 8: Connection options for controlling how tables are ordered 
Option
JoinAlgorithmVariable
DAM_CONN_OPTION_INDEX_OPTIMIZATION
Use this option to disable the default optimization that the OpenAccess SDK SQL engine provides to identify a search column. This option should not be changed unless your IP uses one of the other methods for getting the condition from the query. Valid values are:
• DAM_INDEX_IGNORE_NONE (default), perform default optimization.
• DAM_INDEX_IGNORE_ALL, disable default optimization.
SkipPickingSearchCondition:
• FALSE (default), option value is DAM_INDEX_IGNORE_NONE.
• TRUE, option value is DAM_INDEX_IGNORE_ALL.
DAM_CONN_OPTION_JOINORDER_USING_FKEY
Use this option to disable the use of OA_FKEYS information when determining the join order. Valid values are:
• DAM_PROCESSING_ON (default), make use of foreign keys in determining the join order as detailed in the Join Processing section.
• DAM_PROCESSING_OFF, ignore foreign keys in determining the join order.
SkipFkeyOrdering:
• FALSE (default), option value is DAM_PROCESSING_ON.
• TRUE, option value is DAM_PROCESSING_OFF.
DAM_CONN_OPTION_JOINORDER_USING_ SEARCHCONDITION
Use this option to disable the use of search conditions when determining the join order. Valid values are:
• DAM_PROCESSING_ON (default), make use of search conditions in determining the join order as detailed in the Join Processing section.
• DAM_PROCESSING_OFF, ignore search conditions in determining the join order.
SkipSearchConditionOrdering:
• FALSE (default), option value is DAM_PROCESSING_ON.
• TRUE, option value is DAM_PROCESSING_OFF.
DAM_CONN_OPTION_JOINORDER_USING_ STARJOIN
Use this option to explicitly enable star join detection and optimization. Valid values are:
• DAM_PROCESSING_ON –enable star join detection and optimization.
• DAM_PROCESSING_OFF (default) – disable star join detection and optimization.
• FALSE (default), option value is DAM_PROCESSING_OFF.
• TRUE, option value is DAM_PROCESSING_ON.
DAM_CONN_OPTION_JOINORDER_USING_STATISTICS
Determines whether the OpenAccess SDK SQL engine considers the cost-based join order algorithm when determining the order to join tables when executing a query.
Valid values are:
• DAM_PROCESSING_OFF (default) – do not use the cost-based join order algorithm to determine the table join order.
• DAM_PROCESSING_ON – use the cost-based join order algorithm to determine the table join order. Whether the cost-based join order algorithm is actually used depends on whether conditions are met in the priority hierarchy for join algorithms. See Join algorithm for more information.
• FALSE (default), option value is DAM_PROCESSING_OFF.
• TRUE, option value is DAM_PROCESSING_ON.
DAM_CONN_OPTION_JOINORDER_VERIFY_INDEX_ ON_JOIN_ CONDITION
Use this option to force the OpenAccess SDK SQL engine to check whether inner tables have an index on the join conditions, when deciding the join ordering based on search condition or foreign keys. If inner tables do not have index on join conditions, the join ordering should not be used. Valid values are:
• DAM_PROCESSING_OFF (default), do not check indexes on join conditions.
• DAM_PROCESSING_ON, check indexes on join conditions.
Example:
If the IP does not have an index on Foreign Keys, setting this option ensures that foreign key tables are not processed as inner tables.
CheckIndexOnJoinCond:
• FALSE (default), option value is DAM_PROCESSING_OFF.
• TRUE, option value is DAM_PROCESSING_ON.