The following examples show how checking for indexes on join conditions affects the join order selection.
Indexes are available for only some join conditions—query has search conditions
SELECT * FROM emp, dept, div
WHERE emp.deptno = dept.deptno AND div.divno = dept.divno AND emp.empno = 2
When checking search condition ordering, if an index is available on emp.empno and dept.deptno, and div.divno does not have an index, the search condition ordering is skipped, and the next option is tried.
Indexes Are Available for Only Some Join Conditions—Query Has Only Join Conditions
SELECT * FROM emp, dept, div
WHERE emp.deptno = dept.deptno AND div.divno = dept.divno
When checking FKEY-based ordering, div is selected first. Assume dept.divno has an index, so dept can be processed next. But if emp.deptno does not have an index, the FKEY-based ordering is skipped and the next option is tried.
Indexes Are Available For Join Conditions
If search condition and FKEY-based processing does not occur, join condition ordering is tried. This is the case if the IP does not expose any foreign key information and the query does not have search conditions.
SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno
If emp.deptnohas a non-unique index and dept.deptno has a unique index, dept is picked to be processed first. This effectively produces the order seen with FKEY ordering (primary table first, foreign table next).
Indexes are available on all join columns and query involves foreign key table with multiple primary tables
Consider a query where t3 has a foreign key relation with multiple primary tables, and indexes are available on primary keys and foreign keys.
SELECT * FROM t1, t3, t2
WHERE t1.id = t3.id1 AND t2.id = t3.id2;
SELECT * FROM t1, t2, t3
WHERE t1.id = t3.id1 AND t2.id = t3.id2;
SELECT * FROM t3, t1, t2
WHERE t1.id = t3.id1 AND t2.id = t3.id2;
Based on the order of tables in query, either t1 or t2 is ordered as the first table. Since an Index is available on all join columns, an additional check is made to see which of the tables is related to already ordered tables when deciding the order of t3 with respect to the remaining t1 or t2 tables. This forces t3 as the next table in the join order followed by the remaining primary table (t1 or t2). If this check was not used, the order could be t1, t2, t3, forcing the t2 table to be processed without any condition.
