如何在Oracle中更改JOIN方法？
问题：
您的查询在多个表上具有JOIN条件时会遇到性能问题，并且希望通过在查询中放置适当的提示来覆盖联接类型。
解：
有三种可能的联接类型：嵌套循环，哈希和排序合并。
嵌套循环联接提示：要调用嵌套循环联接 ，请使用USE_NL提示
示例
EXPLAIN PLAN FOR SELECT /*+ use_nl(students, departments) */ 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 | ----------------------------------------------------------
当将小表连接在一起时，嵌套循环连接通常是最好的。在嵌套循环联接中，一个表被视为“驱动”表。这是联接中的外部表。对于外部驱动表中的每一行，在内部表中的每一行中搜索匹配的行。
哈希加入提示：
要调用哈希联接，请使用USE_HASH提示。为了使优化器使用哈希联接，它必须是等联接条件。当联接大量数据或需要表中很大比例的行时，最好使用散列联接。优化器使用两个表中较小的一个在两个表之间的连接键上构建哈希表。
示例
EXPLAIN PLAN FOR SELECT /*+ use_hash(students, departments) */ 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 |
排序合并加入提示：
要调用排序合并联接，请使用USE_MERGE提示。排序合并联接（如哈希联接）用于联接大量数据。与哈希联接不同，当表之间的联接条件不是等联接时，将使用排序合并联接。哈希联接通常比排序合并联接更好，除非数据已经在两个表上排序
示例
EXPLAIN PLAN FOR SELECT /*+ use_merge(students, departments) */ 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 |
数据准备：用于该问题的数据如下所示。数据完全用于演示目的。
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;示例
CREATE TABLE locations
( location_id NUMBER(4)
, street_address VARCHAR2(40)
, postal_code VARCHAR2(12)
, city VARCHAR2(30)
CONSTRAINT loc_city_nn NOT NULL
, state_province VARCHAR2(25)
, country_id CHAR(2)
) ;示例
CREATE TABLE departments
( department_id NUMBER(4)
, department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL
, professor_id NUMBER(6)
, location_id NUMBER(4)
) ;示例
INSERT INTO departments VALUES ( 10, 'Administration', 200, 1700); INSERT INTO departments VALUES ( 20, 'Teaching', 201, 1800); INSERT INTO departments VALUES ( 30 , 'Purchasing' , 114 , 1700 ); INSERT INTO departments VALUES ( 40 , 'Human Resources' , 203 , 2400 ); INSERT INTO departments VALUES ( 50 , 'Students' , 121 , 1500 ); INSERT INTO departments VALUES ( 60 , 'IT' , 103 , 1400 ); INSERT INTO departments VALUES ( 70 , 'Public Relations' , 204 , 2700 ); INSERT INTO departments VALUES ( 80 , 'Fee collectors' , 145 , 2500 ); INSERT INTO departments VALUES ( 90 , 'Executive' , 100 , 1700 ); INSERT INTO departments VALUES ( 100 , 'Finance' , 108 , 1700 ); INSERT INTO departments VALUES ( 110 , 'Accounting' , 205 , 1700 ); INSERT INTO departments VALUES ( 120 , 'Treasury' , NULL , 1700 ); INSERT INTO departments VALUES ( 130 , 'Corporate Tax' , NULL , 1700 ); INSERT INTO departments VALUES ( 140 , 'Control And Credit' , NULL , 1700 ); INSERT INTO departments VALUES ( 160 , 'Benefits' , NULL , 1700 ); INSERT INTO departments VALUES ( 230 , 'Helpdesk' , NULL , 1700 ); COMMIT; ---------------------locations--------------- INSERT INTO locations VALUES ( 1000 , '1297 Via Cola di Rie' , '00989' , 'Roma' , NULL , 'IT' ); INSERT INTO locations VALUES ( 1100 , '93091 Calle della Testa' , '10934' , 'Venice' , NULL , 'IT' ); INSERT INTO locations VALUES ( 1200 , '2017 Shinjuku-ku' , '1689' , 'Tokyo' , 'Tokyo Prefecture' , 'JP' ); INSERT INTO locations VALUES ( 1300 , '9450 Kamiya-cho' , '6823' , 'Hiroshima' , NULL , 'JP' ); INSERT INTO locations VALUES ( 1400 , '2014 Jabberwocky Rd' , '26192' , 'Southlake' , 'Texas' , 'US' ); INSERT INTO locations VALUES ( 1500 , '2011 Interiors Blvd' , '99236' , 'South San Francisco' , 'California' , 'US' ); INSERT INTO locations VALUES ( 1600 , '2007 Zagora St' , '50090' , 'South Brunswick' , 'New Jersey' , 'US' ); INSERT INTO locations VALUES ( 1700 , '2004 Charade Rd' , '98199' , 'Seattle' , 'Washington' , 'US' ); INSERT INTO locations VALUES ( 1800 , '147 Spadina Ave' , 'M5V 2L7' , 'Toronto' , 'Ontario' , 'CA' ); INSERT INTO locations VALUES ( 1900 , '6092 Boxwood St' , 'YSW 9T2' , 'Whitehorse' , 'Yukon' , 'CA' ); INSERT INTO locations VALUES ( 2000 , '40-5-12 Laogianggen' , '190518' , 'Beijing' , NULL , 'CN' ); INSERT INTO locations VALUES ( 2100 , '1298 Vileparle (E)' , '490231' , 'Bombay' , 'Maharashtra' , 'IN' ); INSERT INTO locations VALUES ( 2200 , '12-98 Victoria Street' , '2901' , 'Sydney' , 'New South Wales' , 'AU' ); INSERT INTO locations VALUES ( 2300 , '198 Clementi North' , '540198' , 'Singapore' , NULL , 'SG' ); INSERT INTO locations VALUES ( 2400 , '8204 Arthur St' , NULL , 'London' , NULL , 'UK' ); INSERT INTO locations VALUES ( 2500 , 'Magdalen Centre, The Oxford Science Park' , 'OX9 9ZB' , 'Oxford' , 'Oxford' , 'UK' ); INSERT INTO locations VALUES ( 2600 , '9702 Chester Road' , '09629850293' , 'Stretford' , 'Manchester' , 'UK' ); INSERT INTO locations VALUES ( 2700 , 'Schwanthalerstr. 7031' , '80925' , 'Munich' , 'Bavaria' , 'DE' ); INSERT INTO locations VALUES ( 2800 , 'Rua Frei Caneca 1360 ' , '01307-002' , 'Sao Paulo' , 'Sao Paulo' , 'BR' ); INSERT INTO locations VALUES ( 2900 , '20 Rue des Corps-Saints' , '1730' , 'Geneva' , 'Geneve' , 'CH' ); INSERT INTO locations VALUES ( 3000 , 'Murtenstrasse 921' , '3095' , 'Bern' , 'BE' , 'CH' ); INSERT INTO locations VALUES ( 3100 , 'Pieter Breughelstraat 837' , '3029SK' , 'Utrecht' , 'Utrecht' , 'NL' ); INSERT INTO locations VALUES ( 3200 , 'Mariano Escobedo 9991' , '11932' , 'Mexico City' , 'Distrito Federal,' , 'MX' ); COMMIT;