Using Join-Order Directives with Views

When you tune queries that access views, consider where you place the ORDERED optimizer directive. Two cases can affect join order when you use views:

• If the ORDERED directive is included in the definition of a view, the directive affects only the tables in the view. 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;

  The ORDERED directive specifies that the emp and job table are joined in that order. Because of the view definition, the emp and job tables must also be joined in exactly that order if you execute the following query:

  SELECT * from dept, emp_job_view,  project
     WHERE dept.dept_no = project.dept_num
     emp_job_view.job_num = project.job_num;

  Any join order that places the emp table immediately before the job table is permitted, thus any of the following join orders might occur:

  • emp, job, dept, project
  • emp, job, project, dept
  • project, emp, job, dept
  • dept, emp, job, project
  • dept, project, emp, job
  • project, dept, emp, job
• If an ORDERED directive appears in a query that accesses a view that is not defined with the ORDERED directive, the query directive specifies that the join order of the tables in the query should be as listed in the SELECT statement and the join order of the tables in the view should be as listed within the view.

  Consider a view defined as follows:

  CREATE VIEW emp_job_view AS
     SELECT
     emp.job_num, job.job_name
     FROM emp, job
     WHERE emp.job_num = job.job_num;

  If you run the following query against this view, the join order is dept, project, emp, job:

  SELECT {+ORDERED}
     * FROM dept, project, emp_job_view
     WHERE dept.dept_no = project.dept_num
     AND emp_job_view.job_num = project.job_num;

  If the view cannot be folded into the query, the database server executes the query against the view and stores the result in a temporary table. Consider the result of running the previous query against a view defined as follows:

  CREATE VIEW emp_job_view2 AS
     SELECT DISTINCT
     emp.job_num, job.job_name
     FROM emp,job
     WHERE emp.job_num = job.job_num;

  When the query is executed, the join order of tables is dept, project, temp_table.