Enterprise Edition Home | Express Edition Home | Previous Page | Next Page   Optimizer Directives > Types of Directives That Are Included in SQL Statements >

Join-Order Directives

The join-order directive ORDERED forces the optimizer to join tables in the order that the SELECT statement lists them.

Effect of Join Order on Join Plan

By specifying the join order, you might affect more than just how tables are joined. For example, consider the following query:

SELECT --+ORDERED, AVOID_FULL(e)
* FROM employee e, department d
WHERE e.dept_no = d.dept_no AND e.salary > 5000

In this example, the optimizer chooses to join the tables with a hash join. However, if you arrange the order so that the second table is employee (and must be accessed by an index), the hash join is not feasible.

SELECT --+ORDERED, AVOID_FULL(e)
* FROM department d, employee e
WHERE e.dept_no = d.dept_no AND e.salary > 5000;

The optimizer chooses a nested-loop join in this case.

Join Order When You Use Views

Two cases can affect join order when you use views:

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]