The OpenAccess SDK SQL engine supports functions to identify expressions in the WHERE clause of the query and to make this information available to the IP developer. For example, assume we have query Q1 of the form:
SELECT * FROM emp
WHERE (deptno=20 OR deptno=30) AND (hiredate >'January 1,1997')
Also assume that the column deptno is marked as indexed and that the equal operator is marked as supported. The OpenAccess SDK SQL engine supports different levels of optimization:
• Default. The OpenAccess SDK SQL engine always attempts to identify column(s) with the most restricting expression and passes this expression to the IP. In the Q1 example, the OpenAccess SDK SQL engine passes the expression on column deptno to the IP. The IP sees this as a condition list with elements (=, 20) and (=, 30).
• Column-specific restrictions. The IP can ask for additional restriction conditions on individual columns. In this example, the IP can ask for restrictions on column hiredate. The IP gets this as a condition list with elements:
(>,' January 1,1997')
The condition list implies that the returned expression appears in an AND relationship with the rest of the WHERE clause.
• Advanced optimization. The IP can ask for conditions in the WHERE clause in the form of AND or OR expressions. This option allows for greater flexibility in the type of queries that can be optimized, but requires a little more logic in the IP. With this method you have access to the complete WHERE clause expression normalized into a set of AND expressions or a set of OR expressions.
• SQL pass-through. In this mode, the IP is responsible for fully executing the SQL query specified by the user. The OpenAccess SDK SQL engine handles all the parsing and validation but leaves the execution to the IP. See Implementing SQL pass-through mode for information on working in this mode.
