The IP can perform additional optimization by using the dam_getRestrictionList function with a column handle to get any additional intersection constraints on a column. A restriction list on a column implies that every row the IP returns should satisfy at least one condition in the restriction list. Restrictions on a column are available if that column appears in the WHERE clause as an AND expression. The AND expression may contain a set of OR conditions on the same column. For example, the restriction list on column last_name in the following query:
WHERE first_name='Joe' AND ( last_name= 'Smith' or last_name='Tims')
would result in { (=,’Smith’), (=,’Tims')}, which means that the IP should build a row where the last_name is Smith and build a row where the last_name is Tims. If in this example the fist name is marked as having an index, then the default optimization would return a set of conditions with a single condition list which would contain an expression on column name that is {=, ‘Joe’} and the IP would build two rows that are:
name='Joe' AND last_name='Smith'
name='Joe' AND last_name='Tims'
Another example of using the restriction list is to use time type information in a query to limit your search space. For example, assume you want to retrieve time based data from a table called ARCHIVE. The TAG column is indexed and the query is:
SELECT * FROM ARCHIVE WHERE TAG='T1' AND TIME BETWEEN 'Jan 1' AND 'Jan 2'
In this case, the IP receives a set of condition lists on column TAG with one condition list containing the condition set {(=, 't1')}. At this point, the IP can ask for restriction on the TIME column by calling dam_getRestrictionList. This returns the set {(>=, 'Jan 1', <=, 'Jan 2)} that the IP can use to further limit the amount of data processed. You may ask for restrictions on any number of columns.
To implement fully optimized execution of the query, you may build and evaluate a row for every combination of the condition list and restriction list(s). Because the relationship between the condition lists is that of AND, you should decide to use at least one of the lists. This may result in the IP returning rows that don’t match the other conditions, but the OpenAccess SDK SQL engine filters these out.
For example, if you have a set of condition lists with lists {s1}, {s2}, {s3} and you have two restriction lists r1={r11, r12} and r2={r21, r22, r23), you will build rows with these 18 combinations:
1) s1 AND {r11} AND {r21}
2) s1 AND {r11} AND {r22}
3) s1 AND {r11} AND {r23}
4) s1 AND {r12} AND {r21}
5) s1 AND {r12} AND {r22}
6) s1 AND {r12} AND {r23}
...
16) s3 AND {r12} AND {r21}
17) s3 AND {r12} AND {r22}
18) s3 AND {r12} AND {r23}
For this example, assume the query is:
SELECT * FROM emp WHERE (name='Joe' OR name='Bob' OR name='Tom') AND
(dept = 'Eng' or dept='finance') AND (div='USA' OR div='UK' OR div='CAN')
and, the column name has an index that is selected as the optimal index that results in one of the following:
• The set of condition lists s1={name,=,'Joe'},s2={name,=,'Bob'}, s3={name,=,'Tom'}
• The restriction list on column dept = r1={r11=(=,'Eng'), r12=(=,'finance')}
• The restriction list on column div = r2 = {r21=(=,'USA'), r22=(=,'UK'), r23=(=,'CAN')}
A search list can be combined with one or more restriction lists. The simplest case is where each of the lists contains only one element. Then you build a single row where all the conditions are satisfied. You call dam_getRestrictionList only once for each column on which you would like to find a restriction set.
The IP may call dam_setOption to mark a restriction condition list as evaluated. This forces the OpenAccess SDK SQL engine not to evaluate that condition since the IP has already taken care of it by only returning the rows requested. This allows the IP to support specialized data types. For example, if your IP supports TIME with specialized notation, then you will define the column as a CHAR type and use dam_getRestrictionList(TIME) to get all restrictions.
Next, the list is marked evaluated to prevent the OpenAccess SDK SQL engine from evaluating the expressions using the string comparison rules. The IP handles the expression evaluation using the rules that apply to this column to make sure only the rows that match the expressions on TIME are passed to the OpenAccess SDK SQL engine.
The OpenAccess SDK SQL engine’s ability to disable the processing of an expression is a valuable feature for supporting custom data types, where in the schema, a column is defined as one of the standard types but processed as a special type.
