Join-Order Directive

Join-Order Directive

Element	Description	Restrictions	Syntax
comments	Text to document the directive	Must appear between comment symbols	Character string

For example, the following query forces the database server to join the dept and job tables and then join the result with the emp table:

SELECT --+ ORDERED
   name, title, salary, dname
   FROM dept, job, emp WHERE title = 'clerk' AND loc = 'Palo Alto' 
   AND emp.dno = dept.dno 
   AND emp.job= job.job;

When your query involves a view, the placement of the ORDERED join-order directive determines whether you are specifying a partial- or total-join order.

• Specifying partial-join order when you create a view

  If you use the ORDERED directive when you create a view, the base tables are joined contiguously in the order of the view definition.

  For all subsequent queries on the view, the database server joins the base tables contiguously in the order specified in the view definition. When used in a view, the ORDERED directive does not affect the join order of other tables named in the FROM clause in a query.

• Specifying total-join order when you query a view

  When you specify the ORDERED join-order directive in a query that uses a view, all tables are joined in the order specified, even those tables that form views. If a view is included in the query, the base tables are joined contiguously in the order of the view definition. For examples of ORDERED with views, refer to your HCL OneDB™ Performance Guide.