The condition should be on an index column. The OpenAccess SDK SQL engine selects the best search condition, using the following criteria:
1. Select the search condition with the = operator.
2. Select the condition on a unique index column.
3. Select the condition with the best cardinality (high cardinality index is preferred). Note that the cardinality of a unique index would represent the total number of records in the table.
The following queries show the selection process for search condition based ordering:
SELECT ... FROM dept, div, emp
WHERE emp.empno = 2 AND emp.deptno=dept.deptno AND dept.divno=div.divno
1. SELECT emp as the first table to be processed because it has a search condition (empno=2).
2. SELECT dept as the next table because it has a relationship condition with the emp table.
3. SELECT div next because it has a relationship condition with the dept table.
The query is processed in this order: emp,dept,div
SELECT ... FROM dept, div, emp
WHERE div.divno = 1 AND emp.empno = 2 AND emp.deptno=dept.deptno
AND dept.divno=div.divno.
1. SELECT emp as the first table to be processed because the index on search condition (empno=2) has higher cardinality than the index on search condition (divno=1).
2. SELECT dept as the next table because it has a relationship condition with the emp table.
3. SELECT div next because it has a relationship condition with the dept table.
