How to change the JOIN order in Oracle?

Problem:

You have a performance issue with a queryhaving JOIN conditions on multiple tables, and the Oracle optimizer is not choosing the join order you wanted.

Solution:

Oracle has two hints the ORDERED hint, and the LEADING hint that can be used to influence the join order used within a query.

ORDERED Hint

You are running a SQL to join two tables, student and departments, as you want to get the department names for each student. By placing an ORDERED hint into the query, you can see how the hint alters the execution access path.

Example

EXPLAIN PLAN FOR
SELECT first_name, department_name
  FROM students e, departments d
WHERE e.department_id = d.department_id;
 
SELECT *
FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'BASIC'));

Output

----------------------------------------------------------
| Id  | Operation                    | Name              |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |
|   1 |  MERGE JOIN                  |                   |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |
|   4 |   SORT JOIN                  |                   |
|   5 |    VIEW                      | index$_join$_001  |
|   6 |     HASH JOIN                |                   |
|   7 |      INDEX FAST FULL SCAN    | stu_DEPARTMENT_IX |
|   8 |      INDEX FAST FULL SCAN    | stu_NAME_IX       |
----------------------------------------------------------

Example

EXPLAIN PLAN FOR
SELECT /*+ ordered */ first_name, department_name
  FROM students e, departments d
WHERE e.department_id = d.department_id;

Output

-----------------------------------------------------
| Id  | Operation               | Name              |
-----------------------------------------------------
|   0 | SELECT STATEMENT        |                   |
|   1 |  HASH JOIN              |                   |
|   2 |   VIEW                  | index$_join$_001  |
|   3 |    HASH JOIN            |                   |
|   4 |     INDEX FAST FULL SCAN| stu_DEPARTMENT_IX |
|   5 |     INDEX FAST FULL SCAN| stu_NAME_IX       |
|   6 |   TABLE ACCESS FULL     | DEPARTMENTS       |
-----------------------------------------------------

LEADING Hint

As with the example using the ORDERED hint, you have the same control to specify the join order of the query. The difference with the LEADING hint is that you specify the join order from within the hint itself, while with the ORDERED hint, it is specified in the FROM clause of the query

Example

EXPLAIN PLAN FOR
SELECT /*+ leading(departments, students) */  first_name, department_name
  FROM students e, departments d
WHERE e.department_id = d.department_id;
 
 
SELECT *
FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'BASIC'));

Output

----------------------------------------------------------
| Id  | Operation                    | Name              |
----------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |
|   1 |  MERGE JOIN                  |                   |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |
|   4 |   SORT JOIN                  |                   |
|   5 |    VIEW                      | index$_join$_001  |
|   6 |     HASH JOIN                |                   |
|   7 |      INDEX FAST FULL SCAN    | stu_DEPARTMENT_IX |

Hints will save the optimizer the time of having to process all of the possible join orders in determining the optimal join order. Hints can improve query performance, especially as the number of tables to join within a query increases.

When using either of these hints, you instruct the optimizer about the join order of the tables. Because of this, it is critically important that you know that the hint will improve the query’s performance. Oracle recommends, where possible, to use the LEADING hint over the ORDERED hint, as the LEADING hint has more versatility built in. When specifying the ORDERED hint, you specify the join order from the list of tables in the FROM clause, while with the LEADING hint, you specify the join order within the hint itself.

Data Preparation: Data used for the problem is shown below. The data is completely made up for demonstration purpose.

DROP TABLE students;
COMMIT;

CREATE TABLE students
    ( student_id     NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25) 
    , email          VARCHAR2(40) 
    , phone_number   VARCHAR2(20)
    , join_date      DATE 
    , class_id       VARCHAR2(20)  
    , fees           NUMBER(8,2)
    , professor_id   NUMBER(6)
    , department_id  NUMBER(4) 
    ) ;

Example

CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);

COMMIT;