Specifying a join order v16

Include the ORDERED directive to instruct the query optimizer to join tables in the order in which they're listed in the FROM clause. If you don't include the ORDERED keyword, the query optimizer chooses the order in which to join the tables.

For example, the following command allows the optimizer to choose the order in which to join the tables listed in the FROM clause:

SELECT e.ename, d.dname, h.startdate
  FROM emp e, dept d, jobhist h
  WHERE d.deptno = e.deptno
  AND h.empno = e.empno;

The following command instructs the optimizer to join the tables in the order specified:

SELECT /*+ ORDERED */ e.ename, d.dname, h.startdate
  FROM emp e, dept d, jobhist h
  WHERE d.deptno = e.deptno
  AND h.empno = e.empno;

In the ORDERED version of the command, EDB Postgres Advanced Server first joins emp e with dept d before joining the results with jobhist h. Without the ORDERED directive, the query optimizer selects the join order.