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;
Published on 04-Dec-2020 11:07:18
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP