数据结构
网络
关系数据库管理系统
操作系统
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 | ----------------------------------------------------------
Explore our latest online courses and learn new skills at your own pace. Enroll and become a certified expert to boost your career.
示例
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+ 次查看
