The default optimization that is done by the OpenAccess SDK SQL engine is to attempt to identify expressions on a column or columns that are part of an index, which the IP can use to reduce the number of rows retrieved. The index selection is based on cost-based optimization that looks at the uniqueness and cardinality properties of indexes that are associated with the columns that appear in AND type of expressions with the most restricting expression for passing to the IP.
For basic optimization, all IPs must use this condition list by calling dam_getOptimalIndexAndConditions to get the selected index and the set of conditions on the columns that are part of that index.
Call dam_getOptimalIndexAndConditions if a non-null value is passed for the search column parameter to IP EXECUTE. An expression such as empno=20 is considered a search condition if:
• empno is marked as an indexed column.
• The operator (= in this example) is marked as supported by the IP or is marked supported in the operator support attribute of the OA_COLUMNS entry for this column.
The IP calls dam_getOptimalIndexAndConditions to get the set of search condition lists. Each condition list consists of expressions on one or more columns that are part of the index.
The IP uses functions dam_getFirstCondList and dam_getNextCondList to navigate through this set of condition lists. The IP uses functions dam_getFirstCond and dam_getNextCond to navigate within each list. Each condition list contains conditions on all or some of the columns in the index.
Note: The relationships among the individual condition elements in the list is that of AND.
This means that each row that the OpenAccess SDK SQL engine builds for a condition list must satisfy all the conditions in that list and must then be validated using the dam_isTargetRow function. The order of the conditions corresponds to the order of the columns in the index.
Note: The relationships among the condition lists is that of OR. This means the IP builds a set of rows for each of the condition lists.
The optimal index is selected based on the index information provided in the schema. Specifically, the OpenAccess SDK SQL engine attempts to find expressions that apply to columns that have the best index on them. A best index is one that encompasses the most columns in the query and where the index is marked as being unique. If a unique index cannot be found, then the OpenAccess SDK SQL engine looks for an index with the highest cardinality. Cardinality is the number of unique values in an index.
The column handle for which a search list exists is passed into the IP during the EXECUTE call. It is NULL if an optimal index could be identified in the query.
The following functions can be used by the IP to implement the optimization:
• dam_getOptimalIndexAndConditions
This function gets the index and the conditions (such as TAG='T1', TAG='T2')
• dam_getFirstCondList, dam_getNextCondList
These functions are used to navigate through the set of condition lists. For each condition list, use the next set of functions to retrieve all of the AND conditions on all of the columns that must be satisfied for a row to be considered.
• dam_getFirstCond, dam_getNextCond
These functions are used to navigate through the condition expressions in a condition list.
• dam_describeCondEx
This function retrieves information about the condition. Information includes the value of the condition, the type of the condition (such as =, >), and the type of the data (such as integer, character). The right variables are only filled in for the BETWEEN and LIKE operators. Otherwise, the information is returned in the left variables. The type can take OR combinations of the following values:
SQL_OP_NOT, SQL_OP_SMALLER, SQL_OP_GREATER, SQL_OP_LIKE, SQL_OP_ISNULL, SQL_OP_EQUAL. To check which of the operators are present, use bitwise AND operator (&).
For example, if the condition is VAL >= 10, then *piLeftType = (SQL_OP_EQUAL | SQL_OP_GREATER) and *piLeftVal = 10 & piRightType = NULL. If the condition is VAL BETWEEN 2 AND 10, then *piLeftType = (SQL_OP_GREATER | SQL_OP_EQUAL), *piLeftData = 2, *piRightType = = (SQL_OP_SMALLER | SQL_OP_EQUAL), *piRightData = 10.
• dam_describeIndex
This function is used to obtain the name and properties of the optimal index.
• dam_describeIndexCol
This function is used to obtain the name and the sequence of the individual columns that make up an index.
• dam_getFirstIndexCol, dam_getNextIndexCol
This function is used to navigate through the list of columns making up an index.
• dam_describeCol, dam_describeColDetail
This function is used to obtain information about columns that are part of the conditions.
The optimization on a column is enabled only if the column is marked as having an index and by marking the equality and/or other operators reported as supported by IP GETSUPPORT. Once an operator is marked as supported, the OpenAccess SDK SQL engine expects the IP to handle that operator on any column in any table. If an IP cannot handle the operator in the list of conditions for the current table, then it must read each row of the table from the data source and use the dam_evaluateColCond function. This function takes as input the condition value, the operator, and the value for the search column of the current row. The dam_evaluateColCond function returns TRUE if the value satisfies the condition, or FALSE.
Note: Support flags OP_EQUAL, OP_NOT, OP_SMALLER, OP_GREATER, OP_BETWEEN, OP_LIKE, and OP_NULL returned by IP GETSUPPORT are global, in that they apply to the processing of all indexed columns. To define operator support at individual column level, use the OA_SUPPORT attribute of the OA_COLUMNS table.
