Join-Order Directive

Use the ORDERED join-order directive to force the optimizer to join tables or views in the order in which they are referenced in the FROM clause of the query.

Join-Order Directive

1  ORDERED?  comments
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;

Because no predicates occur between the dept table and the job table, this query forces the database server to construct a Cartesian product.

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.

Because of ordering requirements for OUTER joins, in ANSI-compliant joined queries that specify the RIGHT OUTER JOIN or FULL OUTER JOIN keywords, the ORDERED join-order directive is ignored, but it is listed under Directives Not Followed in the explain output file.