In the optimal case, the IP should only read the rows with NAME equal to SCOTT, TOM, and JOE, and then process them through the complete WHERE clause to reduce it to TOM and JOE due to the LESS THAN constraint on the value column. In general, the fewer the rows read by the IP and processed through the expression processor, the faster the query is executed. The OpenAccess SDK SQL engine provides the functionality necessary to support this type of optimization. The OpenAccess SDK SQL engine supports this functionality in two ways:
• By determining the column in the query that is most restrictive (in the example, NAME). For this column the OpenAccess SDK SQL engine determines the values it can take. In the example, the set {(=, SCOTT), (=, TOM), (=, JOE)} is identified and passed down to the IP.
• By exposing functions to allow the IP to know about other restrictions on other columns in the WHERE clause, and by returning these restrictions to the IP. These functions are referred to as the optimization support functions.
This section discusses implementing an IP that can better handle expressions similar to the one in this example query. It is important to realize that the level of optimization that is achievable is limited by the data access methods that are available to the IP. If your data source only supports sequential access, then you have to read all the rows to find the ones you want. If your data source supports access by one or more keys, then you can take advantage of constraints on those keys to quickly get to the candidate row.
In the example, the NAME column can be used as a key to access the current value data. The IP tries to limit the rows read by checking to see if search conditions exist on the NAME column. If they exist, only rows with NAME values equal to the values that are specified in the SQL statement are read; otherwise, all rows are read.
