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:

• The ORDERED directive is inside the view.

  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:

  • emp, job, dept, project
  • emp, job, project, dept
  • project, emp, job, dept
  • dept, emp, job, project
  • dept, project, emp, job
  • project, dept, emp, job
• The ORDERED directive is in a query that contains a view.

  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.