Mega Code Archive

Analyze and autotrace full outer join and union

SQL> CREATE TABLE EMP( 2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 ); Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT( 2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); 1 row created. SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); 1 row created. SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); 1 row created. SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); 1 row created. SQL> SQL> SQL> update emp 2 set deptno = 9 3 where deptno = 10; 3 rows updated. SQL> SQL> SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> SQL> alter table dept add constraint dept_pk primary key(deptno); Table altered. SQL> SQL> analyze table emp compute statistics; Table analyzed. SQL> SQL> analyze table dept compute statistics; Table analyzed. SQL> SQL> set autotrace on explain SQL> SQL> select empno, ename, dept.deptno, dname 2 from emp, dept 3 where emp.deptno(+) = dept.deptno 4 UNION ALL 5 select empno, ename, emp.deptno, null 6 from emp, dept 7 where emp.deptno = dept.deptno(+) 8 and dept.deptno is null 9 order by 1, 2, 3, 4 10 / EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE SALES 7782 CLARK 9 EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH 7900 JAMES 30 SALES EMPNO ENAME DEPTNO DNAME ---------- ---------- ---------- -------------- 7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS 16 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1556511399 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 28 | 1666 | 7 | | 1 | SORT ORDER BY | | 28 | 1666 | 4 | | 2 | UNION-ALL | | | | | |* 3 | HASH JOIN OUTER | | 14 | 784 | 3 | | 4 | TABLE ACCESS FULL | DEPT | 4 | 92 | 1 | | 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 1 | |* 6 | FILTER | | | | | | 7 | NESTED LOOPS OUTER| | 14 | 882 | 1 | | 8 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | |* 9 | INDEX UNIQUE SCAN| DEPT_PK | 1 | 23 | | ----------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO") 6 - filter("DEPT"."DEPTNO" IS NULL) 9 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+)) Note ----- - cpu costing is off (consider enabling it) SQL> SQL> select empno, ename, nvl(dept.deptno,emp.deptno), dname 2 from emp FULL OUTER JOIN dept on ( emp.deptno = dept.deptno ) 3 order by 1, 2, 3, 4 4 / EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME ---------- ---------- --------------------------- -------------- 7369 SMITH 20 RESEARCH 7499 ALLEN 30 SALES 7521 WARD 30 SALES 7566 JONES 20 RESEARCH 7654 MARTIN 30 SALES 7698 BLAKE 30 SALES 7782 CLARK 9 7788 SCOTT 20 RESEARCH 7839 KING 9 7844 TURNER 30 SALES 7876 ADAMS 20 RESEARCH EMPNO ENAME NVL(DEPT.DEPTNO,EMP.DEPTNO) DNAME ---------- ---------- --------------------------- -------------- 7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 9 10 ACCOUNTING 40 OPERATIONS 16 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1591132751 -------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15 | 825 | 7 | | 1 | SORT ORDER BY | | 15 | 825 | 7 | | 2 | VIEW | | 15 | 825 | 4 | | 3 | UNION-ALL | | | | | |* 4 | HASH JOIN OUTER | | 14 | 882 | 3 | | 5 | TABLE ACCESS FULL| EMP | 14 | 560 | 1 | | 6 | TABLE ACCESS FULL| DEPT | 4 | 92 | 1 | |* 7 | FILTER | | | | | | 8 | TABLE ACCESS FULL| DEPT | 1 | 23 | 1 | |* 9 | TABLE ACCESS FULL| EMP | 5 | 65 | 1 | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+)) 7 - filter( NOT EXISTS (SELECT /*+ UNNEST */ 0 FROM "EMP" "EMP" WHERE "EMP"."DEPTNO"=:B1)) 9 - filter("EMP"."DEPTNO"=:B1) Note ----- - cpu costing is off (consider enabling it) SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.