The join-order directive ORDERED forces the optimizer to join tables in the order that the SELECT statement lists them.
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.
Two cases can affect join order when you use views:
The ORDERED directive inside a view affects the join order of only the tables inside the view. The tables in the view must be joined contiguously. Consider the following view and query:
CREATE VIEW emp_job_view as SELECT {+ORDERED} emp.job_num, job.job_name FROM emp, job WHERE emp.job_num = job.job_num; SELECT * from dept, emp_job_view, project WHERE dept.dept_no = project.dept_num AND emp_job_view.job_num = project.job_num;
The ORDERED directive specifies that the emp table come before the job table. The directive does not affect the order of the dept and project table. Therefore, all possible join orders are as follows:
If an ORDERED directive appears in a query that contains a view, the join order of the tables in the query are the same as they are listed in the SELECT statement. The tables within the view are joined as they are listed within the view.
In the following query, the join order is dept, project, emp, job:
CREATE VIEW emp_job_view AS SELECT emp.job_num, job.job_name FROM emp, job WHERE emp.job_num = job.job_num; SELECT {+ORDERED} * FROM dept, project, emp_job_view WHERE dept.dept_no = project.dept_num AND emp_job_view.job_num = project.job_num;
An exception to this rule is when the view cannot be folded into the query, as in the following example:
CREATE VIEW emp_job_view2 AS SELECT DISTINCT emp.job_num, job.job_name FROM emp,job WHERE emp.job_num = job.job_num;
In this example, the database server executes the query and puts the result in a temporary table. The order of tables in this query is dept, project, temp_table.