- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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;
- Related Articles
- How to change the JOIN Method in Oracle ?
- How to change order of items in MySQL?
- How to change the order of Pandas DataFrame columns?
- How to join two data frames with the same row order using dplyr in R?
- How to change the order of plots in Pandas hist command?
- How to change the row order in an R data frame?
- How to change the order of elements in a list in R?
- How to change the order of bars in bar chart in R?
- How to change the order of a matrix in increasing order based on a single column?
- How to change the order of columns in an R data frame?
- How to change the order of boxplot by means using ggplot2 in R?
- How to PIVOT results in Oracle?
- How to UNPIVOT results in Oracle?
- How to change the order of independent variables for regression summary output in R?
- Difference between oracle golden gate and oracle active guard in the oracle
