Search condition order

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.