How to change the JOIN Method in Oracle ?

OracleSoftware & CodingProgramming

Problem:

You have a performance issue with a queryhaving JOIN conditions on multiple tables, and you wish to override the join type by placing the appropriate hint in the query.

Solution:

There are three possible types of joins: nested loops, hash, and sort merge.

Nested Loops Join Hint:  To invoke a nested loops join, use the USE_NL hint

Example

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'));

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       | ----------------------------------------------------------

The nested loops join is usually best when joining small tables together. In a nested loops join, one table is considered the “driving” table. This is the outer table in the join. For each row in the outer, driving table, each row in the inner table is searched for matching rows.

Hash Join Hint:

To invoke a hash join, use the USE_HASH hint. For the optimizer to use a hash join, it must be an equijoin condition. Hash joins are best used when joining large amounts of data or where a large percentage of rows from a table is needed. The smaller of the two tables is used by the optimizer to build a hash table on the join key between the two tables.

Example

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'));

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       |

Sort Merge Join Hint:

To invoke a sort merge join, use the USE_MERGE hint.Sort merge joins, like hash joins, are used to join a large volume of data. Unlike the hash join, the sort merge join is used when the join condition between the tables is not an equijoin. The hash join will generally perform better than the sort merge join, unless the data is already sorted on the two tables

Example

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'));

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       |

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;

Example

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)
    ) ;

Example

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)
    ) ;

Example

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;
raja
Published on 05-Dec-2020 05:49:23
Advertisements