1 / 80

Oracle Join Techniques

Oracle Join Techniques. Carl Dudley University of Wolverhampton, UK Oracle ACE Director carl.dudley@wlv.ac.uk. Introduction. Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002 Oracle ACE Director Beta tester – Oracle8, 9, 10, 11, 12

kayo
Télécharger la présentation

Oracle Join Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Join Techniques Carl Dudley University of Wolverhampton, UK Oracle ACE Director carl.dudley@wlv.ac.uk Carl Dudley – University of Wolverhampton

  2. Introduction Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002 Oracle ACE Director Beta tester – Oracle8, 9, 10, 11, 12 Regular Presenter at Oracle Conferences Consultant and Trainer Technical Editor for a number of Oracle texts UK Oracle User Group Official Member of IOUC Day job – University of Wolverhampton, UK ANSI and Oracle Joins Two-way Outer Joins Join and Filter Conditions Chaining on the Outer Join Hash Join Trees Anti Joins and Semi Joins Lateral Joins Nested Loops Joins Join Elimination Carl Dudley – University of Wolverhampton

  3. The emp and dept Tables dept DEPTNO DNAME LOC ------ -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- ----- ----- ------ 7934 MILLER CLERK 7782 23-JAN-1982 1300 10 7782 CLARK MANAGER 7839 09-JUN-1981 2450 10 7839 KING PRESIDENT 17-NOV-1981 5000 10 7369 SMITH CLERK 7902 17-DEC-1980 800 20 7876 ADAMS CLERK 7788 12-JAN-1983 1100 20 7566 JONES MANAGER 7839 02-APR-1981 2975 20 7902 FORD ANALYST 7566 03-DEC-1981 3000 20 7788 SCOTT ANALYST 7566 09-DEC-1982 3000 20 7900 JAMES CLERK 7698 03-DEC-1981 950 30 7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30 7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30 7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30 7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30 emp Carl Dudley – University of Wolverhampton

  4. ANSI Joins • ANSI defined join syntax in ANSI SQL2 Standard (1992) • Oracle finally fully implemented this syntax in Oracle9i • Types of joins specified by additional keywords • INNER JOIN • OUTER JOIN (LEFT RIGHT FULL) • CROSS JOIN • UNION JOIN (not supported by Oracle) Carl Dudley – University of Wolverhampton

  5. Inner Joins – ANSI and Oracle Syntax Union Join • Not in common use and Oracle has no support but can be simulated • Number of columns in each select clause must match • Use NULLs to pad out the number of columns • Equivalent ANSI – based joins SELECT ename,dname,dept.deptno FROM emp JOIN dept ON dept.deptno = empa.deptno; SELECT o.*, NULL,...,NULL FROM orders o UNION SELECT NULL,...,NULL, l.* FROM line_items l SELECT ename,dname,deptno FROM emp NATURAL JOIN dept; SELECT ename,dname,deptno FROM emp NATURAL JOIN dept USING (deptno); • Expected output OCOL1 OCOL2 LCOL1 LCOL2 ----- ----- ----- ----- 11111 AAAAA 22222 BBBBB 33333 CCCCC 88888 XXXXX 99999 YYYYY • Note the absence of the table qualifier fordeptnoin some of the examples • Equivalent Oraclejoin SELECT ename,dname,dept.deptno FROM emp,dept WHERE emp.deptno = dept.deptno; Carl Dudley – University of Wolverhampton

  6. The Join Condition • Join condition (ON clause) must be included • Helps safeguard against inadvertant cartesian products SELECT ename,dname,dept.deptno FROM emp JOIN dept; ORA-00905: missing keyword • But join condition does not have to make any sense • Could cause cartesian products • Oracle join syntax has no protection SELECT ename,dname,dept.deptno FROM emp JOIN dept ON emp.empno = emp.empno; Carl Dudley – University of Wolverhampton

  7. Multi-table Joins • If several tables are to be joined • Use a step-wise process by first joining two of the tables • Then add a further JOIN keyword to join to the third table • Repeat this for each subsequent table • Each join may have its own condition(s) • The joins may be of different types SELECT emp.empno ,emp.ename ,dept.loc ,proj.pname FROM emp JOIN dept ON emp.deptno = dept.deptno JOIN proj ON emp.proj_id = proj.proj_id; Carl Dudley – University of Wolverhampton

  8. LEFT and RIGHT Outer Joins • The driving table is specified with LEFT or RIGHT • This table will have all its rows included • Dummy NULL rows may be included from the other table • Refers to placement of the table name WITHIN the FROM ... JOIN clause • The following join clauses are all equivalent FROM emp,dept WHERE emp.deptno(+) = dept.deptno FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno Carl Dudley – University of Wolverhampton

  9. Modified Employee Data empa EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ -------- ----- ----------- ---- ----- ------- 7782 CLARK MANAGER 7839 09-JUN-1981 2450 10 7788 SCOTT ANALYST 7566 19-APR-1987 3000 20 7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30 7499 ALLEN SAlESMAN 7698 20-FEB-1981 1600 300 30 7902 FORD ANALYST 7566 03-DEC-1981 3000 20 1111 EXTRA CEO 01-JAN-1999 500 dept DEPTNODNAME LOC ----------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Carl Dudley – University of Wolverhampton

  10. RIGHT Outer Joins SELECT ename,dname,dept.deptno FROM empa RIGHT OUTER JOIN dept ON empa.deptno = dept.deptno; ENAME DNAME DEPTNO ------- ------------ ------ CLARK ACCOUNTING 10 SCOTT RESEARCH 20 TURNER SALES 30 FORD RESEARCH 20 ALLEN SALES 30 OPERATIONS 40 • Equivalent Oracle joinstatement SELECT ename,dname,dept.deptno FROM empa,dept WHERE dept.deptno = emp.deptno(+); Carl Dudley – University of Wolverhampton

  11. LEFT Outer Joins SELECT ename,dname,dept.deptno FROM empa LEFT OUTER JOIN dept ON empa.deptno = dept.deptno; ENAME DNAME DEPTNO ------- ------------ ------ CLARK ACCOUNTING 10 SCOTT RESEARCH 20 TURNER SALES 30 FORD RESEARCH 20 ALLEN SALES 30 EXTRA • Equivalent Oracle joinstatement SELECT ename,dname,dept.deptno FROM empa, dept WHERE dept.deptno(+) = empa.deptno; Carl Dudley – University of Wolverhampton

  12. FULL Outer Joins SELECT ename,dname,dept.deptno d_deptno, empa.deptno e_deptno FROM empa FULL OUTER JOIN dept ON empa.deptno = dept.deptno; ENAME DNAME D_DEPTNO E_DEPTNO ------- ------------ -------- -------- CLARK ACCOUNTING 10 10 SCOTT RESEARCH 20 20 TURNER SALES 30 30 FORD RESEARCH 20 20 ALLEN SALES 30 30 EXTRA OPERATIONS 40 All rows included from both tables • Equivalent (ILLEGAL) Oracle joinstatement SELECT ename,dname,dept.deptno,empa.deptno FROM empa, dept WHERE dept.deptno(+) = empa.deptno(+); Carl Dudley – University of Wolverhampton

  13. FULL OUTER JOIN – Oracle11g • Oracle now performs a 'NATIVE' full outer join SELECT ename ,dname FROM empa e FULL OUTER JOIN dept d ON e.deptno = d.deptno; --------------------------------------------------------------- | Id | Operation | Name |Rows |Bytes |Cost(%CPU --------------------------------------------------------------- | 0| SELECT STATEMENT | | 15 | 240 | 7 (15 | 1| VIEW | VW_FOJ_0 | 15 | 240 | 7 (15 |* 2| HASH JOIN FULL OUTER| | 15 | 330 | 7 (15 | 3| TABLE ACCESS FULL | DEPT | 4 | 52 | 3 (0 | 4| TABLE ACCESS FULL | EMPA | 14 | 126 | 3 (0 --------------------------------------------------------------- Carl Dudley – University of Wolverhampton

  14. FULL OUTER JOIN – Oracle10g ALTER SESSION SET optimizer_features_enable = '10.2.0.1'; SELECT ename ,dname FROM empa e FULL OUTER JOIN dept d ON e.deptno = d.deptno; ------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | 15 | 240 | 13| | 1 | VIEW | | 15 | 240 | 13| | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 308 | 7| | 4 | TABLE ACCESS FULL| EMPA | 14 | 126 | 3| | 5 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3| |* 6 | HASH JOIN ANTI | | 1 | 16 | 7| | 7 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3| | 8 | TABLE ACCESS FULL| EMPA | 14 | 42 | 3| ------------------------------------------------------------ Carl Dudley – University of Wolverhampton

  15. FULL OUTER JOIN – Oracle10g (continued) • Statement is transformed to a UNION construct • Generates same execution plan as for 10g FULL OUTER JOIN • Native full outer join available in 10.2.0.3 and 10.2.0.4 with a hint /*+NATIVE_FULL_OUTER_JOIN */ • Undocumented parameter _optimizer_native_full_outer_join SELECT ename ,dname FROM empa e ,dept d WHERE e.deptno = d.deptno(+) UNION ALL SELECT null ,dname FROM dept d2 WHERE NOT EXISTS (SELECT 'x' FROM empa e2 WHERE e2.deptno = d2.deptno); Carl Dudley – University of Wolverhampton

  16. Oracle Join Techniques ANSI and Oracle Joins Two-way Outer Joins Join and Filter Conditions Chaining on the Outer Join Hash Join Trees Anti Joins and Semi Joins Lateral Joins Nested Loops Joins Join Elimination Carl Dudley – University of Wolverhampton

  17. Two-way Outer Joins • project table linked to a cut down modified version of emp, called empb • Project number 3 (DESIGN) has no employees empb EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PROJ_ID ----- ------ -------- ---- ----------- ---- ---- ------ ------- 7782 CLARK MANAGER 7839 09-JUN-1981 2450 10 1 7788 SCOTT ANALYST 7566 19-APR-1987 3000 20 2 7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30 1 7499 ALLEN SAlESMAN 7698 20-FEB-1981 1600 300 30 1 dept proj DEPTNODNAME LOC ----------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON PROJ_ID PNAME START_DATE ------- --------- ----------- 1 BPR 01-JUL-2002 2 MIGRATION 12-OCT-2002 3 DESIGN 01-NOV-2002 Carl Dudley – University of Wolverhampton

  18. Two-way Outer Joins • Show details of employees with their projects and departments • Include projects with no employees and departments with no employees project empb dept (+) (+) Carl Dudley – University of Wolverhampton

  19. ANSITwo-wayOuter Joins – Expected result • Two-way outer joins are allowed but the result may not be as expected • Outer join of empb to both dept and project based on deptno and proj_id values (as requested on the previous slide) should give the following result ENAME HIREDATE DEPTNO DNAME PROJ_ID PNAME ---------- ----------- ------ ----------- ------- --------- ALLEN 20-FEB-1981 30 SALES 1 BPR TURNER 08-SEP-1981 30 SALES 1 BPR CLARK 09-JUN-1981 10 ACCOUNTING 1 BPR SCOTT 19-APR-1987 20 RESEARCH 2 MIGRATION 40 OPERATIONS 3 DESIGN Carl Dudley – University of Wolverhampton

  20. ANSITwo-wayOuter Joins – Missing Rows • Unexpected results? SELECT ename, hiredate,d.deptno,dname,p.proj_id,pname FROM dept d LEFT OUTER JOIN empb e ON e.deptno = d.deptno RIGHT OUTER JOIN proj p ON e.proj_id = p.proj_id; ENAME HIREDATE DEPTNO DNAME PROJ_ID PNAME ---------- ----------- ------ ----------- ------- --------- CLARK 09-JUN-1981 10 ACCOUNTING 1 BPR SCOTT 19-APR-1987 20 RESEARCH 2 MIGRATION ALLEN 20-FEB-1981 30 SALES 1 BPR TURNER 08-SEP-1981 30 SALES 1 BPR 3 DESIGN Carl Dudley – University of Wolverhampton

  21. ANSITwo-wayOuter Joins – Missing Rows (continued) • Reordering the joins still gives another different unexpected result? • Outer joins are not 'symmetrical' SELECT ename, hiredate,d.deptno,dname,p.proj_id,pname FROM proj p LEFT OUTER JOIN empb e ON e.proj_id = p.proj_id RIGHT OUTER JOIN dept d ON e.deptno = d.deptno; ENAME HIREDATE DEPTNO DNAME PROJ_ID PNAME ---------- ----------- ------ ----------- ------- --------- CLARK 09-JUN-1981 10 ACCOUNTING 1 BPR SCOTT 19-APR-1987 20 RESEARCH 2 MIGRATION TURNER 08-SEP-1981 30 SALES 1 BPR ALLEN 20-FEB-1981 30 SALES 1 BPR 40 OPERATIONS Carl Dudley – University of Wolverhampton

  22. ANSITwo-wayOuter Joins - Explanation • Explanation of unexpected behaviour • Examine the result of the first join in the first example • The fifth row does not have a value in the proj_id column • This row can not take part in the final join with the proj table SELECT ename, hiredate,d.deptno,dname,proj_id FROM dept d LEFT OUTER JOIN empb e ON e.deptno = d.deptno; ENAME HIREDATE DEPTNO DNAME PROJ_ID ---------- ----------- ------ ----------- ------- CLARK 09-JUN-1981 10 ACCOUNTING 1 SCOTT 19-APR-1987 20 RESEARCH 2 TURNER 08-SEP-1981 30 SALES 1 ALLEN 20-FEB-1981 30 SALES 1 40 OPERATIONS Carl Dudley – University of Wolverhampton

  23. ANSITwo-wayOuter Joins – use of FULL • Outer joining dept to empb gives a row with NULL in proj_id • This row can not take part in the second outer join • The second join needs to be a FULL OUTER JOIN SELECT ename,hiredate,d.deptno,dname,p.proj_id,pname FROM dept d LEFT OUTER JOIN empb e ON e.deptno = d.deptno FULL OUTER JOIN proj p ON e.proj_id = p.proj_id; ENAME HIREDATE DEPTNO DNAME PROJ_ID PNAME ---------- ----------- ------ ----------- ------- --------- ALLEN 20-FEB-1981 30 SALES 1 BPR TURNER 08-SEP-1981 30 SALES 1 BPR CLARK 09-JUN-1981 10 ACCOUNTING 1 BPR SCOTT 19-APR-1987 20 RESEARCH 2 MIGRATION 40 OPERATIONS 3 DESIGN Carl Dudley – University of Wolverhampton

  24. Two-way Outer Joins – Oracle Style • In 11g, Oracle syntax cannot outer join a table to more than one other table SELECT ename,hiredate,d.deptno,dname,p.proj_id,pname FROM dept d, empb e, proj p WHERE e.deptno(+) = d.deptno AND e.proj_id(+) = p.proj_id; ORA-01417: a table may be outer joined to at most one other table Carl Dudley – University of Wolverhampton

  25. Two-way Outer Joins – Oracle Style (continued) • But in Oracle12c, this would be the output • Cannot be imitated using ANSI syntax ENAME HIREDATE DEPTNO DNAME PROJ_ID PNAME ---------- ----------- ------ ----------- ------- ---------- SCOTT 19-APR-1987 20 RESEARCH 2 MIGRATION ALLEN 20-FEB-1981 30 SALES 1 BPR TURNER 08-SEP-1981 30 SALES 1 BPR CLARK 09-JUN-1981 10 ACCOUNTING 1 BPR 40 OPERATIONS 1 BPR 40 OPERATIONS 2 MIGRATION 40 OPERATIONS 3 DESIGN 30 SALES 2 MIGRATION 30 SALES 3 DESIGN 20 RESEARCH 1 BPR 20 RESEARCH 3 DESIGN 10 ACCOUNTING 2 MIGRATION 10 ACCOUNTING 3 DESIGN Carl Dudley – University of Wolverhampton

  26. Oracle Outer Joins with Subqueries • Show each department with its longest serving employee • Subqueries are not allowed in combination with an outer join condition SELECT ename, hiredate,d.deptno,dname FROM dept d LEFT OUTER JOIN emp e ON e.deptno = d.deptno AND e.hiredate = (SELECT MIN(e.hiredate) FROM emp e WHERE e.deptno = d.deptno); SELECT ename, hiredate,d.deptno,dname FROM dept d, empa e WHERE e.deptno(+) = d.deptno AND e.hiredate(+) = (SELECT MIN(e.hiredate) FROM empa e WHERE e.deptno = d.deptno); ORA-01799: a column may not be outer-joined to a subquery • What about ANSI joins? Carl Dudley – University of Wolverhampton

  27. Oracle Join Techniques ANSI and Oracle Joins Two-way Outer Joins Join and Filter Conditions Chaining on the Outer Join Hash Join Trees Anti Joins and Semi Joins Lateral Joins Nested Loops Joins Join Elimination Carl Dudley – University of Wolverhampton

  28. Join and Filter Conditions • Oracle uses the ON clause for join conditions • Conditions specified in a WHERE clause are treated as filter conditions • Filter conditions can also be specified in the ON (or USING) clause • Join operations and conditions are effectively processed before the WHERE clause conditions Carl Dudley – University of Wolverhampton

  29. Oracle Join and Filter Conditions SELECT dept.deptno,dname,ename,job FROM dept, empa WHERE empa.deptno(+) = dept.deptno AND empa.job = 'SALESMAN'; DEPTNO DNAME ENAME JOB ------ ----------- -------- -------- 30 SALES TURNER SALESMAN 30 SALES ALLEN SALESMAN Filter applied after the join SELECT dept.deptno,dname,ename,job FROM dept, empa WHERE empa.deptno(+) = dept.deptno AND empa.job(+) = 'SALESMAN'; DEPTNO DNAME ENAME JOB ------ ----------- -------- -------- 10 ACCOUNTING 20 RESEARCH 30 SALES TURNER SALESMAN 30 SALES ALLEN SALESMAN 40 OPERATIONS Filter applied during the join • Somewhat awkward syntax Carl Dudley – University of Wolverhampton

  30. ANSI Join and Filter Conditions SELECT dept.deptno,dname,ename,job FROM dept LEFT OUTER JOIN empa ON empa.deptno = dept.deptno WHERE empa.job = 'SALESMAN‘; DEPTNO DNAME ENAME JOB ------ ---------- -------- -------- 30 SALES ALLEN SALESMAN 30 SALES TURNER SALESMAN Filter applied after the join • Easier to understand due to separation of join conditions from filter conditions SELECT dept.deptno,dname,ename,job FROM dept LEFT OUTER JOIN empa ON empa.deptno = dept.deptno AND empa.job = 'SALESMAN'; DEPTNO DNAME ENAME JOB ------ ----------- ------- -------- 30 SALES TURNER SALESMAN 30 SALES ALLEN SALESMAN 40 OPERATIONS 20 RESEARCH 10 ACCOUNTING Filter applied during the join Note the three outer joined rows - one row for each department having no salesmen Carl Dudley – University of Wolverhampton

  31. Oracle Outer Joins and OR • Oracle syntax cannot cope SELECT dname ,ename ,job ,loc ,sal FROM emp e ,dept d WHERE e.deptno(+) = d.deptno AND (sal(+) < 1000 OR loc(+) = 'DALLAS') ORDER BY dname; AND (sal(+) < 1000 OR loc(+) = 'DALLAS') * ERROR : ORA-01719:outer join operator (+) not allowed in operand of OR or IN Carl Dudley – University of Wolverhampton

  32. ANSI Outer Joins and OR • ANSI join handles OR with outer join SELECT dname ,ename ,job ,loc ,sal FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno AND (sal < 1000 OR loc = 'DALLAS') ORDER BY dname; DNAME ENAME JOB LOC SAL -------------- ---------- --------- ------------- ---- ACCOUNTING NEW YORK OPERATIONS BOSTON RESEARCH ADAMS CLERK DALLAS 1100 RESEARCH FORD ANALYST DALLAS 3000 RESEARCH JONES MANAGER DALLAS 2975 RESEARCH SMITH CLERK DALLAS 800 RESEARCH SCOTT ANALYST DALLAS 3000 SALES JAMES CLERK CHICAGO 950 Carl Dudley – University of Wolverhampton

  33. Oracle Join Techniques ANSI and Oracle Joins Two-way Outer Joins Join and Filter Conditions Chaining on the Outer Join Hash Join Trees Anti Joins and Semi Joins Lateral Joins Nested Loops Joins Join Elimination Carl Dudley – University of Wolverhampton

  34. 'Chaining on' the Outer Join attendance student course • All courses have attendances, all attendances have offerings • Hence outer join is never necessary when joining offering and attendance • Some students do not have attendances • Query : Show ALL students along with their courses Carl Dudley – University of Wolverhampton

  35. Data in the Sample Tables STUDENT_ID STUDENT_LNAME ---------- ------------- 1111 BROWN 2299 ADAMS 4568 COX 5556 TYLER 6789 ROSE student course • Two students do not have any attendances • All courses and attendances are related COURSE_ID START_DATE --------- ---------- 1001 14-DEC-11 1002 12-JUL-12 1003 20-NOV-12 attendance COURSE_ID STUDENT_ID --------- ---------- 1001 1111 1002 2299 1003 2299 1002 6789 1003 1111 Carl Dudley – University of Wolverhampton

  36. Joining course and attendance SELECT c.course_id c_course_id ,a.course_id a_course_id ,a.student_id FROM course c INNER JOIN attendance a ON c.course_id = a.course_id; SELECT o.course_id c_course_id ,a.course_id a_course_id ,a.student_id a_student_id FROM attendance a LEFT OUTER JOIN course c ON a.course_id = c.course_id; • Outer join is completely unnecessary as all rows match C_COURSE_ID A_COURSE_ID A_STUDENT_ID ----------- ----------- ------------ 1001 1001 1111 1002 1002 2299 1003 1003 2299 1002 1002 6789 1003 1003 1111 Carl Dudley – University of Wolverhampton

  37. Joining attendance and student • Outer join is necessary to include students without attendances SELECT s.student_id s_student_id ,s.student_lname s_student_lname ,a.student_id a_student_id ,a.course_id a_course_id FROM student s LEFT OUTER JOIN attendance a ON s.student_id = a.student_id; S_STUDENT_ID S_LNAME A_STUDENT_ID A_COURSE_ID ------------ ------- ------------ ----------- 1111 BROWN 1111 1001 2299 ADAMS 2299 1002 2299 ADAMS 2299 1003 6789 ROSE 6789 1002 1111 BROWN 1111 1003 4568 COX 5556 TYLER Carl Dudley – University of Wolverhampton

  38. Join Three Tables - Inner join 'loses' students SELECT s.student_id s_student_id,s.student_lname s_lname ,a.student_id a_student_id,a.course_id a_course_id ,c.course_id c_course_id,c.start_date FROM student s LEFT OUTER JOIN attendance a ON s.student_id = a.student_id INNER JOIN course c ON c.course_id = a.course_id; • Rows are lost by the inner join • Oracle is smart enough not to perform the outer join S_STUDENT_ID S_LNAME A_STUDENT_ID A_COURSE_ID C_COURSE_ID START_DATE ------------ ------- ------------ ----------- ----------- ---------- 1111 BROWN 1111 1001 1001 14-DEC-11 2299 ADAMS 2299 1002 1002 12-JUL-12 2299 ADAMS 2299 10031003 20-NOV-12 6789 ROSE 6789 1002 1002 12-JUL-12 1111 BROWN 1111 1003 1003 20-NOV-12 ----------------------------------------------- | Id | Operation | Name |Rows | ----------------------------------------------- | 0| SELECT STATEMENT | | 5| |* 1| HASH JOIN | | 5| |* 2| HASH JOIN | | 5| | 3| TABLE ACCESS FULL| COURSE | 3| | 4| TABLE ACCESS FULL| ATTENDANCE | 5| | 5| TABLE ACCESS FULL | STUDENT | 5| ----------------------------------------------- Carl Dudley – University of Wolverhampton

  39. Chaining on the OUTER JOIN SELECT s.student_id s_student_id,s.student_lname s_lname ,a.student_id a_student_id,a.course_id a_course_id ,c.course_id c_course_id,c.start_date FROM student s LEFT OUTER JOIN attendance a ON s.student_id = a.student_id LEFT OUTER JOIN course c ON c.course_id = a.course_id; ----------------------------------------------- | Id | Operation | Name |Rows | ----------------------------------------------- | 0| SELECT STATEMENT | | 5| |* 1| HASH JOIN OUTER | | 5| |* 2| HASH JOIN OUTER | | 5| | 3| TABLE ACCESS FULL| STUDENT | 5| | 4| TABLE ACCESS FULL| ATTENDANCE | 5| | 5| TABLE ACCESS FULL | COURSE | 3| ----------------------------------------------- S_STUDENT_ID S_LNAME A_STUDENT_ID A_COURSE_ID C_COURSE_ID START_DATE ------------ ------- ------------ ----------- ----------- ---------- 1111 BROWN 1111 1001 1001 14-DEC-11 2299 ADAMS 2299 1002 1002 12-JUL-12 2299 ADAMS 2299 10031003 20-NOV-12 6789 ROSE 6789 1002 1002 12-JUL-12 1111 BROWN 1111 1003 1003 20-NOV-12 4568 COX 5556 TYLER • Outer join performed between attendance and course when not strictly necessary Carl Dudley – University of Wolverhampton

  40. Avoiding the second OUTER JOIN SELECT s.student_id s_student_id,s.student_lname s_lname ,a.student_id a_student_id,a.course_id a_course_id ,c.course_id c_course_id,c.start_date FROM course c JOIN attendance a ON c.course_id = a.course_id RIGHT OUTER JOIN student s ON s.student_id = a.student_id; • Inner join performed first • Outer join preserves all student rows S_STUDENT_ID S_LNAME A_STUDENT_ID A_COURSE_ID C_COURSE_ID START_DATE ------------ ------- ------------ ----------- ----------- ---------- 1111 BROWN 1111 1001 1001 14-DEC-11 2299 ADAMS 2299 1002 1002 12-JUL-12 2299 ADAMS 2299 10031003 20-NOV-12 6789 ROSE 6789 1002 1002 12-JUL-12 1111 BROWN 1111 1003 1003 20-NOV-12 4568 COX 5556 TYLER ------------------------------------------------ | Id | Operation | Name |Rows | ------------------------------------------------ | 0| SELECT STATEMENT | | 5| |* 1| HASH JOIN OUTER | | 5| | 2| TABLE ACCESS FULL | STUDENT | 5| | 3| VIEW | | 5| |* 4| HASH JOIN | | 5| | 5| TABLE ACCESS FULL| COURSE | 3| | 6| TABLE ACCESS FULL| ATTENDANCE | 5| ------------------------------------------------ Carl Dudley – University of Wolverhampton

  41. Ordering the joins 'Right' SELECT s.student_id s_student_id,s.student_lname s_lname ,a.student_id a_student_id,a.course_id a_course_id ,c.course_id c_course_id,c.start_date FROM attendance a RIGHT OUTER JOIN student s ON s.student_id = a.student_idJOIN course c ON c.course_id = a.course_id; S_STUDENT_ID S_LNAME A_STUDENT_ID A_COURSE_ID C_COURSE_ID START_DATE ------------ ------- ------------ ----------- ----------- ---------- 1111 BROWN 1111 1001 1001 14-DEC-11 2299 ADAMS 2299 1002 1002 12-JUL-12 2299 ADAMS 2299 10031003 20-NOV-12 6789 ROSE 6789 1002 1002 12-JUL-12 1111 BROWN 1111 1003 1003 20-NOV-12 • Right join would be performed first? • Inner join would then lose the outer joined student rows • No outer joins performed ----------------------------------------------- | Id | Operation | Name |Rows | ----------------------------------------------- | 0| SELECT STATEMENT | | 5| |* 1 HASH JOIN | | 5| |* 2| HASH JOIN | | 5| | 3| TABLE ACCESS FULL| COURSE | 3| | 4| TABLE ACCESS FULL| ATTENDANCE | 5| | 5| TABLE ACCESS FULL | STUDENT | 5| ----------------------------------------------- Carl Dudley – University of Wolverhampton

  42. Oracle Join Techniques ANSI and Oracle Joins Two-way Outer Joins Join and Filter Conditions Chaining on the Outer Join Hash Join Trees Anti Joins and Semi Joins Lateral Joins Nested Loops Joins Join Elimination Carl Dudley – University of Wolverhampton

  43. Processing a Hash Join Build Table (customers) Hash table Probe Table (orders) Carl Dudley – University of Wolverhampton

  44. Join Trees for T1 T2 T3 T4 • Right deep tree • Left deep tree Hash table built on T4 probed by result of T1 join T2 join T3 Output of T1 join T2 join T3 probed by T4 Output of T1 join T2 probed by T3 Hash table built on T3 probed by result of T1 join T2 T4 T4 Hash table built on T1 probed by T2 Hash table built on T2 probed by T1 T3 T3 T1 T1 T2 T2 Carl Dudley – University of Wolverhampton

  45. Join Trees for T1 T2 T3 T4 (continued) • Zig-zag tree • Bushy tree T4 T3 T1 T1 T2 T2 T3 T4 Carl Dudley – University of Wolverhampton

  46. Summary of Hash Join Trees • Left-deep • Result sets are formed before build of next hash table • Workarea discarded after each subsequent join • Only one workarea used • Right-deep • Hash tables on T3 and T4 can be built 'in parallel' with hash table on T1 • Three workareas can be used • Could be useful when the results of joins are larger than the two sets of rows being joined Carl Dudley – University of Wolverhampton

  47. Tree Combinations • Optimizer normally considers only left-deep trees • Left deep joins cover most requirements • Number of left-deep trees = n! • Total number of join trees = (2n-2)!/(n-1)! • Can Oracle be forced to perform a bushy tree? Carl Dudley – University of Wolverhampton

  48. Four Table Join Scenario sa ID NAME WA_ID -- ---- ----- 1 x 1 2 y 2 3 z 3 : : : wa WA_ID WA_NAME ----- ------- 1 name1 2 name2 3 name3 : : p P_ID P_NAME WA_ID PT_ID ---- ------ ----- ----- 1 pname1 1 1 2 pname2 2 2 3 pname3 3 3 : : : : pt PT_ID PT_NAME ----- ------- 1 ptname1 2 ptname2 3 ptname3 : : Carl Dudley – University of Wolverhampton

  49. Right Deep Join Tree SELECT /*+GATHER_PLAN_STATISTICS */ name FROM sa JOIN wa ON wa.wa_id = sa.wa_id JOIN p ON wa.wa_id = p.wa_id JOIN pt ON pt.pt_id = p.pt_id; • Oracle uses hints internally to force a right deep join in this case SWAP_JOIN_INPUTS(@"SEL$EE94F965" "PT"@"SEL$3") SWAP_JOIN_INPUTS(@"SEL$EE94F965" "WA"@"SEL$1") SWAP_JOIN_INPUTS(@"SEL$EE94F965" "SA"@"SEL$1") Internally generated hints ----------------------------- | SELECT STATEMENT | | | HASH JOIN | | | TABLE ACCESS FULL | SA | | HASH JOIN | | | TABLE ACCESS FULL | WA | | HASH JOIN | | | TABLE ACCESS FULL| PT | | TABLE ACCESS FULL| P | ----------------------------- Carl Dudley – University of Wolverhampton

  50. Left Deep Join Tree SELECT /*+GATHER_PLAN_STATISTICS no_swap_join_inputs(wa) no_swap_join_inputs(p) no_swap_join_inputs(pt) no_swap_join_inputs(sa)*/ name FROM sa JOIN wa ON wa.wa_id = sa.wa_id JOIN p ON wa.wa_id = p.wa_id JOIN pt ON pt.pt_id = p.pt_id; • Left deep tree can be forced using hints ----------------------------- | SELECT STATEMENT | | | HASH JOIN | | | HASH JOIN | | | HASH JOIN | | | TABLE ACCESS FULL| PT | | TABLE ACCESS FULL| P | | TABLE ACCESS FULL | WA | | TABLE ACCESS FULL | SA | ----------------------------- Carl Dudley – University of Wolverhampton

More Related