SQL> create table t_emp

2  as

3  select *

4  from  emp, (select rownum no from dual connect by level <= 100000);

테이블이 생성되었습니다.

SQL>

SQL> alter session set workarea_size_policy = manual;

세션이 변경되었습니다.

SQL>

SQL> alter session set sort_area_size = 1048576;

세션이 변경되었습니다.

SQL>

SQL> set autotrace on

SQL>

SQL> select *

2  from (

3    select no, empno, ename, job, mgr, sal

4         , avg(sal) over (partition by to_char(no), deptno) avg_sal

5    from   t_emp

6  )

7  where  no = 1

8  order by sal desc ;

NO      EMPNO ENAME      JOB              MGR        SAL    AVG_SAL

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

1       7839 KING       PRESIDENT                  5000 2916.66667

1       7902 FORD       ANALYST         7566       3000 2258.33333

1       7566 JONES      MANAGER         7839       2975 2258.33333

1       7698 BLAKE      MANAGER         7839       2850 1566.66667

1       7782 CLARK      MANAGER         7839       2450 2916.66667

1       7499 ALLEN      SALESMAN        7698       1600 1566.66667

1       7844 TURNER     SALESMAN        7698       1500 1566.66667

1       7934 MILLER     CLERK           7782       1300 2916.66667

1       7654 MARTIN     SALESMAN        7698       1250 1566.66667

1       7521 WARD       SALESMAN        7698       1250 1566.66667

1       7900 JAMES      CLERK           7698        950 1566.66667

NO      EMPNO ENAME      JOB              MGR        SAL    AVG_SAL

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

1       7369 SMITH      CLERK           7902        800 2258.33333

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

Execution Plan

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

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

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

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

|   0 | SELECT STATEMENT     |       |  1045K|    77M|       | 46561   (2)|

|   1 |  SORT ORDER BY       |       |  1045K|    77M|   192M| 46561   (2)|

|*  2 |   VIEW               |       |  1045K|    77M|       | 24328   (2)|

|   3 |    WINDOW SORT       |       |  1045K|    77M|   192M| 24328   (2)|

|   4 |     TABLE ACCESS FULL| T_EMP |  1045K|    77M|       |  2094   (4)|

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

Predicate Information (identified by operation id):

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

2 - filter("NO"=1)

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

230  recursive calls

78  db block gets

8022  consistent gets

28332  physical reads

0  redo size

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

1  sorts (disk)

12  rows processed

SQL>

SQL>

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

2         D.DEPTNO,

3         D.DNAME,

4         E.ENAME,

5         E.SAL,

6         E.JOB,

7         RANK() OVER(PARTITION BY D.DEPTNO ORDER BY E.SAL) SAL_RANK

8  FROM   DEPT D,

9         EMP  E

10  WHERE  D.DEPTNO = E.DEPTNO

11  ORDER BY E.SAL  DESC

12  ;

DEPTNO DNAME          ENAME             SAL JOB         SAL_RANK

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

10 ACCOUNTING     KING             5000 PRESIDENT          3

20 RESEARCH       FORD             3000 ANALYST            3

20 RESEARCH       JONES            2975 MANAGER            2

30 SALES          BLAKE            2850 MANAGER            6

10 ACCOUNTING     CLARK            2450 MANAGER            2

30 SALES          ALLEN            1600 SALESMAN           5

30 SALES          TURNER           1500 SALESMAN           4

10 ACCOUNTING     MILLER           1300 CLERK              1

30 SALES          MARTIN           1250 SALESMAN           2

30 SALES          WARD             1250 SALESMAN           2

30 SALES          JAMES             950 CLERK              1

DEPTNO DNAME          ENAME             SAL JOB         SAL_RANK

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

20 RESEARCH       SMITH             800 CLERK              1

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

Execution Plan

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

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

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

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

|   0 | SELECT STATEMENT               |         |    12 |   408 |     8  (38)|

|   1 |  SORT ORDER BY                 |         |    12 |   408 |     8  (38)|

|   2 |   WINDOW SORT                  |         |    12 |   408 |     8  (38)|

|   3 |    MERGE JOIN                  |         |    12 |   408 |     6  (17)|

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

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

|*  6 |     SORT JOIN                  |         |    12 |   252 |     4  (25)|

|   7 |      TABLE ACCESS FULL         | EMP     |    12 |   252 |     3   (0)|

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

Predicate Information (identified by operation id):

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

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

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

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

317  recursive calls

0  db block gets

62  consistent gets

2  physical reads

0  redo size

1077  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

8  sorts (memory)

0  sorts (disk)

12  rows processed

SQL>