analyze and autotrace full outer join and union : analyze « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

Home
Oracle PL/SQL Tutorial
1.Introduction
2.Query Select
3.Set
4.Insert Update Delete
5.Sequences
6.Table
7.Table Joins
8.View
9.Index
10.SQL Data Types
11.Character String Functions
12.Aggregate Functions
13.Date Timestamp Functions
14.Numerical Math Functions
15.Conversion Functions
16.Analytical Functions
17.Miscellaneous Functions
18.Regular Expressions Functions
19.Statistical Functions
20.Linear Regression Functions
21.PL SQL Data Types
22.PL SQL Statements
23.PL SQL Operators
24.PL SQL Programming
25.Cursor
26.Collections
27.Function Procedure Packages
28.Trigger
29.SQL PLUS Session Environment
30.System Tables Data Dictionary
31.System Packages
32.Object Oriented
33.XML
34.Large Objects
35.Transaction
36.User Privilege
Oracle PL/SQL Tutorial » SQL PLUS Session Environment » analyze 
29.33.5.analyze and autotrace full outer join and union
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(72),
  8      COMM NUMBER(72),
  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);

row created.

SQL> INSERT INTO EMP VALUES(7499'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 160030030);

row created.

SQL> INSERT INTO EMP VALUES(7521'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 125050030);

row created.

SQL> INSERT INTO EMP VALUES(7566'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7654'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250140030);

row created.

SQL> INSERT INTO EMP VALUES(7698'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);

row created.

SQL> INSERT INTO EMP VALUES(7782'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);

row created.

SQL> INSERT INTO EMP VALUES(7788'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7839'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

row created.

SQL> INSERT INTO EMP VALUES(7844'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500030);

row created.

SQL> INSERT INTO EMP VALUES(7876'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7900'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);

row created.

SQL> INSERT INTO EMP VALUES(7902'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);

row created.

SQL> INSERT INTO EMP VALUES(7934'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

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

row created.

SQL> INSERT INTO DEPT VALUES (20'RESEARCH', 'DALLAS');

row created.

SQL> INSERT INTO DEPT VALUES (30'SALES', 'CHICAGO');

row created.

SQL> INSERT INTO DEPT VALUES (40'OPERATIONS', 'BOSTON');

row created.

SQL>
SQL>
SQL> update emp
  2     set deptno = 9
  3   where deptno = 10;

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 1234
 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  |
-----------------------------------------------------------------
|   SELECT STATEMENT      |         |    28 |  1666 |     |
|   |  SORT ORDER BY        |         |    28 |  1666 |     |
|   |   UNION-ALL           |         |       |       |       |
|*  |    HASH JOIN OUTER    |         |    14 |   784 |     |
|   |     TABLE ACCESS FULL | DEPT    |     |    92 |     |
|   |     TABLE ACCESS FULL | EMP     |    14 |   462 |     |
|*  |    FILTER             |         |       |       |       |
|   |     NESTED LOOPS OUTER|         |    14 |   882 |     |
|   |      TABLE ACCESS FULL| EMP     |    14 |   560 |     |
|*  |      INDEX UNIQUE SCAN| DEPT_PK |     |    23 |       |
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
   - filter("DEPT"."DEPTNO" IS NULL)
   - 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 1234
  4  /

     EMPNO ENAME      NVL(DEPT.DEPTNO,EMP.DEPTNODNAME
---------- ---------- --------------------------- --------------
      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.DEPTNODNAME
---------- ---------- --------------------------- --------------
      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  |
--------------------------------------------------------------
|   SELECT STATEMENT      |      |    15 |   825 |     |
|   |  SORT ORDER BY        |      |    15 |   825 |     |
|   |   VIEW                |      |    15 |   825 |     |
|   |    UNION-ALL          |      |       |       |       |
|*  |     HASH JOIN OUTER   |      |    14 |   882 |     |
|   |      TABLE ACCESS FULL| EMP  |    14 |   560 |     |
|   |      TABLE ACCESS FULL| DEPT |     |    92 |     |
|*  |     FILTER            |      |       |       |       |
|   |      TABLE ACCESS FULL| DEPT |     |    23 |     |
|*  |      TABLE ACCESS FULL| EMP  |     |    65 |     |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
   - filterNOT EXISTS (SELECT /*+ UNNEST */ FROM "EMP" "EMP"
              WHERE "EMP"."DEPTNO"=:B1))
   - 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.
29.33.analyze
29.33.1.analyze table compute statistics
29.33.2.analyze compute statistics on table with index
29.33.3.Analyze table with user defined column type
29.33.4.analyze table compute statistics for table for all indexes for all indexed columns
29.33.5.analyze and autotrace full outer join and union
29.33.6.analyze and autotrace single column key and multi-column key
29.33.7.analyze and autotrace table with primary key
29.33.8.analyze index t_idx validate structure
29.33.9.analyze table TABLENAME compute statistics;
29.33.10.analyze table after creating index
29.33.11.analyze table estimate statistics
29.33.12.analyze table students compute statistics
29.33.13.analyze table t compute statistics for table for columns id;
29.33.14.analyze table t compute statistics for table, for all indexes, for all indexed columns
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.