SQL> set autotrace on

SQL>

SQL> -- Sort Aggregate

SQL> SELECT SUM(SAL), MAX(SAL), MIN(SAL)

2  FROM   EMP;

SUM(SAL)   MAX(SAL)   MIN(SAL)

---------- ---------- ----------

24925       5000        800

Execution Plan

----------------------------------------------------------

----------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     4 |     3   (0)|

|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |

|   2 |   TABLE ACCESS FULL| EMP  |    12 |    48 |     3   (0)|

----------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

548  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> -- Sort Order By

SQL> SELECT *

2  FROM   EMP

3  ORDER BY SAL DESC;

EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM

---------- ---------- --------- ---------- -------- ---------- ----------

DEPTNO

----------

7839 KING       PRESIDENT            81/11/17       5000

10

7902 FORD       ANALYST         7566 81/12/03       3000

20

7566 JONES      MANAGER         7839 81/04/02       2975

20

EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM

---------- ---------- --------- ---------- -------- ---------- ----------

DEPTNO

----------

7698 BLAKE      MANAGER         7839 81/05/01       2850

30

7782 CLARK      MANAGER         7839 81/06/09       2450

10

7499 ALLEN      SALESMAN        7698 81/02/20       1600        300

30

EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM

---------- ---------- --------- ---------- -------- ---------- ----------

DEPTNO

----------

7844 TURNER     SALESMAN        7698 81/09/08       1500          0

30

7934 MILLER     CLERK           7782 82/01/23       1300

10

7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400

30

EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM

---------- ---------- --------- ---------- -------- ---------- ----------

DEPTNO

----------

7521 WARD       SALESMAN        7698 81/02/22       1250        500

30

7900 JAMES      CLERK           7698 81/12/03        950

30

7369 SMITH      CLERK           7902 80/12/17        800

20

12 개의 행이 선택되었습니다.

Execution Plan

----------------------------------------------------------

----------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    12 |   444 |     4  (25)|

|   1 |  SORT ORDER BY     |      |    12 |   444 |     4  (25)|

|   2 |   TABLE ACCESS FULL| EMP  |    12 |   444 |     3   (0)|

----------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

1270  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

12  rows processed

SQL>

SQL> -- Sort Group By

SQL> SELECT DEPTNO, JOB, SUM(SAL), MAX(SAL), MIN(SAL)

2  FROM   EMP

3  GROUP BY DEPTNO, JOB

4  ORDER BY DEPTNO, JOB;

DEPTNO JOB         SUM(SAL)   MAX(SAL)   MIN(SAL)

---------- --------- ---------- ---------- ----------

10 CLERK           1300       1300       1300

10 MANAGER         2450       2450       2450

10 PRESIDENT       5000       5000       5000

20 ANALYST         3000       3000       3000

20 CLERK            800        800        800

20 MANAGER         2975       2975       2975

30 CLERK            950        950        950

30 MANAGER         2850       2850       2850

30 SALESMAN        5600       1600       1250

9 개의 행이 선택되었습니다.

Execution Plan

----------------------------------------------------------

----------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    11 |   165 |     4  (25)|

|   1 |  SORT GROUP BY     |      |    11 |   165 |     4  (25)|

|   2 |   TABLE ACCESS FULL| EMP  |    12 |   180 |     3   (0)|

----------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

907  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

9  rows processed

SQL>

SQL> -- Sort Unique

SQL> SELECT /*+ ORDERED USE_NL(DEPT) */ *

2  FROM   DEPT

3  WHERE  DEPTNO IN (SELECT /*+ UNNEST */ DEPTNO

4                    FROM   EMP  WHERE  JOB = 'CLERK');

DEPTNO DNAME          LOC

---------- -------------- -------------

10 ACCOUNTING     NEW YORK

20 RESEARCH       DALLAS

30 SALES          CHICAGO

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     2 |    62 |     5  (20)|

|   1 |  NESTED LOOPS                |         |     2 |    62 |     5  (20)|

|   2 |   SORT UNIQUE                |         |     2 |    22 |     3   (0)|

|*  3 |    TABLE ACCESS FULL         | EMP     |     2 |    22 |     3   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)|

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("JOB"='CLERK')

5 - access("DEPTNO"="DEPTNO")

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

12  consistent gets

0  physical reads

0  redo size

627  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

3  rows processed

SQL>

SQL> -- Union

SQL> SELECT JOB, MGR

2  FROM   EMP

3  WHERE  DEPTNO = 10

4  UNION

5  SELECT JOB, MGR

6  FROM   EMP

7  WHERE  DEPTNO = 20;

JOB              MGR

--------- ----------

ANALYST         7566

CLERK           7782

CLERK           7902

MANAGER         7839

PRESIDENT

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     6 |    90 |     8  (63)|

|   1 |  SORT UNIQUE        |      |     6 |    90 |     8  (63)|

|   2 |   UNION-ALL         |      |       |       |            |

|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)|

|*  4 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)|

-----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("DEPTNO"=10)

4 - filter("DEPTNO"=20)

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

14  consistent gets

0  physical reads

0  redo size

569  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

5  rows processed

SQL>

SQL> --Minus

SQL> SELECT JOB, MGR

2  FROM   EMP

3  WHERE  DEPTNO = 10

4  MINUS

5  SELECT JOB, MGR

6  FROM   EMP

7  WHERE  DEPTNO = 20;

JOB              MGR

--------- ----------

CLERK           7782

PRESIDENT

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     3 |    90 |     8  (63)|

|   1 |  MINUS              |      |       |       |            |

|   2 |   SORT UNIQUE       |      |     3 |    45 |     4  (25)|

|*  3 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)|

|   4 |   SORT UNIQUE       |      |     3 |    45 |     4  (25)|

|*  5 |    TABLE ACCESS FULL| EMP  |     3 |    45 |     3   (0)|

-----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter("DEPTNO"=10)

5 - filter("DEPTNO"=20)

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

14  consistent gets

0  physical reads

0  redo size

526  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

2  rows processed

SQL>

SQL> -- Sort Join

SQL> SELECT DISTINCT DEPTNO

2  FROM   EMP

3  ORDER BY DEPTNO;

DEPTNO

----------

10

20

30

Execution Plan

----------------------------------------------------------

----------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     3 |     9 |     5  (40)|

|   1 |  SORT UNIQUE       |      |     3 |     9 |     4  (25)|

|   2 |   TABLE ACCESS FULL| EMP  |    12 |    36 |     3   (0)|

----------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

469  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

3  rows processed

SQL>

SQL> -- Distinct

SQL> SELECT /*+ ORDERED USE_MERGE(E) */ *

2  FROM   DEPT D, EMP  E

3  WHERE  D.DEPTNO = E.DEPTNO;

DEPTNO DNAME          LOC                EMPNO ENAME      JOB

---------- -------------- ------------- ---------- ---------- ---------

MGR HIREDATE        SAL       COMM     DEPTNO

---------- -------- ---------- ---------- ----------

10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT

81/11/17       5000                    10

10 ACCOUNTING     NEW YORK            7782 CLARK      MANAGER

7839 81/06/09       2450                    10

10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK

7782 82/01/23       1300                    10

DEPTNO DNAME          LOC                EMPNO ENAME      JOB

---------- -------------- ------------- ---------- ---------- ---------

MGR HIREDATE        SAL       COMM     DEPTNO

---------- -------- ---------- ---------- ----------

20 RESEARCH       DALLAS              7902 FORD       ANALYST

7566 81/12/03       3000                    20

20 RESEARCH       DALLAS              7369 SMITH      CLERK

7902 80/12/17        800                    20

20 RESEARCH       DALLAS              7566 JONES      MANAGER

7839 81/04/02       2975                    20

DEPTNO DNAME          LOC                EMPNO ENAME      JOB

---------- -------------- ------------- ---------- ---------- ---------

MGR HIREDATE        SAL       COMM     DEPTNO

---------- -------- ---------- ---------- ----------

30 SALES          CHICAGO             7900 JAMES      CLERK

7698 81/12/03        950                    30

30 SALES          CHICAGO             7844 TURNER     SALESMAN

7698 81/09/08       1500          0         30

30 SALES          CHICAGO             7654 MARTIN     SALESMAN

7698 81/09/28       1250       1400         30

DEPTNO DNAME          LOC                EMPNO ENAME      JOB

---------- -------------- ------------- ---------- ---------- ---------

MGR HIREDATE        SAL       COMM     DEPTNO

---------- -------- ---------- ---------- ----------

30 SALES          CHICAGO             7521 WARD       SALESMAN

7698 81/02/22       1250        500         30

30 SALES          CHICAGO             7499 ALLEN      SALESMAN

7698 81/02/20       1600        300         30

30 SALES          CHICAGO             7698 BLAKE      MANAGER

7839 81/05/01       2850                    30

12 개의 행이 선택되었습니다.

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    12 |   684 |     6  (17)|

|   1 |  MERGE JOIN                  |         |    12 |   684 |     6  (17)|

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)|

|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)|

|*  4 |   SORT JOIN                  |         |    12 |   444 |     4  (25)|

|   5 |    TABLE ACCESS FULL         | EMP     |    12 |   444 |     3   (0)|

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("D"."DEPTNO"="E"."DEPTNO")

filter("D"."DEPTNO"="E"."DEPTNO")

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

11  consistent gets

0  physical reads

0  redo size

1547  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

12  rows processed

SQL>

SQL> -- Window Sort

SQL> SELECT EMPNO, ENAME, JOB, MGR, SAL,

2             AVG(SAL) OVER(PARTITION BY DEPTNO)

3  FROM   EMP;

EMPNO ENAME      JOB              MGR        SAL

---------- ---------- --------- ---------- ----------

AVG(SAL)OVER(PARTITIONBYDEPTNO)

-------------------------------

7839 KING       PRESIDENT                  5000

2916.66667

7782 CLARK      MANAGER         7839       2450

2916.66667

7934 MILLER     CLERK           7782       1300

2916.66667

EMPNO ENAME      JOB              MGR        SAL

---------- ---------- --------- ---------- ----------

AVG(SAL)OVER(PARTITIONBYDEPTNO)

-------------------------------

7902 FORD       ANALYST         7566       3000

2258.33333

7369 SMITH      CLERK           7902        800

2258.33333

7566 JONES      MANAGER         7839       2975

2258.33333

EMPNO ENAME      JOB              MGR        SAL

---------- ---------- --------- ---------- ----------

AVG(SAL)OVER(PARTITIONBYDEPTNO)

-------------------------------

7900 JAMES      CLERK           7698        950

1566.66667

7844 TURNER     SALESMAN        7698       1500

1566.66667

7654 MARTIN     SALESMAN        7698       1250

1566.66667

EMPNO ENAME      JOB              MGR        SAL

---------- ---------- --------- ---------- ----------

AVG(SAL)OVER(PARTITIONBYDEPTNO)

-------------------------------

7521 WARD       SALESMAN        7698       1250

1566.66667

7499 ALLEN      SALESMAN        7698       1600

1566.66667

7698 BLAKE      MANAGER         7839       2850

1566.66667

12 개의 행이 선택되었습니다.

Execution Plan

----------------------------------------------------------

----------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

----------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    12 |   348 |     4  (25)|

|   1 |  WINDOW SORT       |      |    12 |   348 |     4  (25)|

|   2 |   TABLE ACCESS FULL| EMP  |    12 |   348 |     3   (0)|

----------------------------------------------------------------

Note

-----

- 'PLAN_TABLE' is old version

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

7  consistent gets

0  physical reads

0  redo size

1120  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

12  rows processed

SQL>

SQL>

SQL>