資料結構
網路
關係資料庫管理系統
作業系統
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 程式設計
C++
C#
MongoDB
MySQL
Javascript
PHP
物理學
化學
生物學
數學
英語
經濟學
心理學
社會學
服裝設計
法律研究如何在 Oracle 中更改 JOIN 順序？
問題
您的查詢在多個表上具有 JOIN 條件，並且存在效能問題，而 Oracle 最佳化器沒有選擇您想要的連線順序。
解決方案
Oracle 有兩個提示：ORDERED 提示和 LEADING 提示，可用於影響查詢中使用的連線順序。
ORDERED 提示
您正在執行一個 SQL 來連線兩個表：student 和 departments，因為您想獲取每個學生的部門名稱。透過在查詢中放置 ORDERED 提示，您可以瞭解提示如何更改執行訪問路徑。
示例
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'));
輸出
---------------------------------------------------------- | 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 | ----------------------------------------------------------
示例
EXPLAIN PLAN FOR SELECT /*+ ordered */ first_name, department_name FROM students e, departments d WHERE e.department_id = d.department_id;
輸出
----------------------------------------------------- | 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 提示
與使用 ORDERED 提示的示例一樣，您可以使用相同的控制來指定查詢的連線順序。LEADING 提示的區別在於，您在提示本身中指定連線順序，而在 ORDERED 提示中，它是在查詢的 FROM 子句中指定的。
示例
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'));
輸出
---------------------------------------------------------- | 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 |
提示將節省最佳化器在確定最佳連線順序時處理所有可能的連線順序的時間。提示可以提高查詢效能，尤其是在查詢中要連線的表數量增加時。
使用這兩個提示中的任何一個時，您都會指示最佳化器表的連線順序。因此，至關重要的是，您需要知道提示將提高查詢的效能。Oracle 建議在可能的情況下，使用 LEADING 提示而不是 ORDERED 提示，因為 LEADING 提示具有更高的通用性。在指定 ORDERED 提示時，您從 FROM 子句中表的列表中指定連線順序，而在 LEADING 提示中，您在提示本身中指定連線順序。
資料準備：問題使用的資料如下所示。資料完全是為了演示目的而編造的。
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) ) ;
示例
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;
2K+ 次檢視
