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:

• 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 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.