The cost-based join order algorithm depends on table statistics for each table referenced in the query. To use this algorithm, the IP must populate the OA_STATISTICS catalog with table statistics. Optionally, the IP can populate the catalog with index statistics for columns.
An entry for each table referenced in the query must exist in the OA_STATISTICS catalog. The table entry must have its OA_TYPE set to SQL_TABLE_STAT, and its OA_CARDINALITY must be set to the number of rows contained in the table. If the table does not contain any rows, set OA_CARDINALITY to 0. A value of NULL means that statistics are not available for that table.
If a column has an index defined for it, index statistics are used for the column. If a column does not have an index or the value of OA_CARDINALITY for the index is NULL, table statistics are used.
Conditions for using cost based join order
To use the cost-based join order algorithm, all the following conditions must be met:
• The query cannot reference a derived table or view. For example, in the following query, the table identified by the alias dpt is a derived table and disqualifies the query for cost-based join ordering:
SELECT * FROM emp, (SELECT * FROM dept) dpt
WHERE emp.deptno=dpt.deptno
• The query must contain at least one join condition using the = operator. For example, the following query contains a join condition using the = operator:
SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno AND emp.salary < dept.mgr_salary
• All tables referenced in the query must have table statistics defined for them.
Note: Index statistics defined for columns are optional.
