h1.소트 발생 Operation
h2.1. Sort aggregate
전체 Row 대상의 집계 쿼리
SQL> select sum(value) from table ;
SQL> select sum(SAL), max(SAL), min(SAL) from scott.emp;
SUM(SAL) MAX(SAL) MIN(SAL)
---------- ---------- ----------
29025 5000 800
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | | ----> Sort Aggregate 발생확인
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
548 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory) -------------------------------------------> 확인
0 sorts (disk) -------------------------------------------> 확인
1 rows processed
h2.2. Sort order by
Order by 쿼리 시 발생
SQL> select name from table order by name ;
SQL> set pagesize 200
SQL> set linesize 200
SQL> select * from scott.emp 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
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
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
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7369 SMITH CLERK 7902 80/12/17 800 20
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 150391907
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 1218 | 4 (25)| 00:00:01 | ----> sort order by 발생 확인.
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1333 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory) -------------------------------------------> 확인
0 sorts (disk) -------------------------------------------> 확인
14 rows processed
h2.3. Sort group by(hash group by)
집계 시 하나의 그룹에 속하는 전체 Row 를 읽고 계산하는게 아니라 건건이 읽어가면서 갱신함
=> Row 가 많아도 그룹 갯수 적으면 Disk Sort 발생하지 않음
SQL> select deptno, job, sum(SAL), max(SAL), min(SAL)
2 from scott.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 6000 3000 3000
20 CLERK 1900 1100 800
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
30 MANAGER 2850 2850 2850
30 SALESMAN 5600 1600 1250
9 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 448 | 4 (25)| 00:00:01 | ----> Sort GROUP BY 발생확인
| 2 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
907 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory) -------------------------------------------> 확인
0 sorts (disk) -------------------------------------------> 확인
9 rows processed
Hash group by와 비교
– 10gR2부터 hash group by 방식이 도입되면서 order by 절을 명시하지 않으면 대부분 hash group by방식으로 처리됨.
SQL> select deptno, job, sum(SAL), max(SAL), min(SAL)
2 from scott.emp
3 group by deptno,job
4 ;
DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)
---------- --------- ---------- ---------- ----------
20 CLERK 1900 1100 800
30 SALESMAN 5600 1600 1250
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
10 PRESIDENT 5000 5000 5000
30 MANAGER 2850 2850 2850
10 CLERK 1300 1300 1300
10 MANAGER 2450 2450 2450
20 ANALYST 6000 3000 3000
9 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 448 | 4 (25)| 00:00:01 | ----> Hash GROUP BY 발생확인
| 2 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
907 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory) -------------------------------------------> 확인
0 sorts (disk) -------------------------------------------> 확인
9 rows processed
hash group by : 정렬을 수행하지 않고 해싱알고리즘 사용.
sort group by : 모든데이터를 정렬하고 집계하는것은 아니며 소팅알고리즘 사용
양쪽 모두 소수일때는 디스크 소트를 발생하지 않음.
hash group by 비활성화 방법
alter system set "_gby_hash_aggregation_enabled"=false;
다음에 접속됨:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> set pagesize 200
SQL> set linesize 200
SQL> set autot on
SQL> alter system set "_gby_hash_aggregation_enabled" = false;
시스템이 변경되었습니다.
SQL> select deptno, job, sum(SAL), max(SAL), min(SAL)
2 from scott.emp
3 group by deptno,job
4 ;
DEPTNO JOB SUM(SAL) MAX(SAL) MIN(SAL)
---------- --------- ---------- ---------- ----------
10 CLERK 1300 1300 1300
10 MANAGER 2450 2450 2450
10 PRESIDENT 5000 5000 5000
20 CLERK 1900 1100 800 ==> JOB명의 정렬 확인
20 ANALYST 6000 3000 3000 ==>
20 MANAGER 2975 2975 2975
30 CLERK 950 950 950
30 MANAGER 2850 2850 2850
30 SALESMAN 5600 1600 1250
9 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 448 | 4 (25)| 00:00:01 | ==>HASH GROUP BY가 아님을 확인
| 2 | TABLE ACCESS FULL| EMP | 14 | 448 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
359 recursive calls
0 db block gets
73 consistent gets
8 physical reads
0 redo size
907 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
9 rows processed
h2.4. Sort unique(hash unique)
DEPT, EMP Table 이 DEPT(DEPTNO):EMP(DEPTNO) = 1:M 관계일 때,
Unnesting 되어서 Where 절 Subquery 가 Join 으로 변환될 때
M:1 의 M 쪽 테이블 이거나
SQL> select * from dept where deptno in (select deptno from emp);
M:1 의 1쪽 테이블이라도 유니크 인덱스가 없으면(서브쿼리 컬럼이 유니크함을 물리적으로 보장받지 못하면)
SQL> select * from emp where deptno in (select deptno from dept);
서브쿼리 안의 deptno 를 unique 하게 만들어 만든 후 Unnesting 후 Join 처리 함
SQL> select /*+ ordered use_nl(dept) */ * from scott.dept
2 where deptno in (select /*+ unnest */ deptno
3 from scott.emp where job = 'CLERK');
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Execution Plan
----------------------------------------------------------
Plan hash value: 1091542497
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 147 | 6 (17)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 147 | 6 (17)| 00:00:01 |
| 2 | SORT UNIQUE | | 4 | 76 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 4 | 76 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 30 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("JOB"='CLERK')
5 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement
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
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
– UNION , MINUS, INTERSECT 같은 집합연산자 사용시
SQL> select job,mgr from scott.emp where deptno=10
2 union
3 select job,mgr from scott.emp where deptno=20;
JOB MGR
--------- ----------
ANALYST 7566
CLERK 7782
CLERK 7788
CLERK 7902
MANAGER 7839
PRESIDENT
6 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3774834881
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 256 | 8 (63)| 00:00:01 |
| 1 | SORT UNIQUE | | 8 | 256 | 8 (63)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 96 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 5 | 160 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
4 - filter("DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select job,mgr from scott.emp where deptno=10
2 minus
3 select job,mgr from scott.emp where deptno=20;
JOB MGR
--------- ----------
CLERK 7782
PRESIDENT
Execution Plan
----------------------------------------------------------
Plan hash value: 3686975449
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 256 | 8 (63)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 3 | 96 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 3 | 96 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 160 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 160 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
5 - filter("DEPTNO"=20)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
2 rows processed
– distinct 연산시 (sort unique, hash unique)
SQL> select distinct deptno from scott.emp order by deptno;
DEPTNO
----------
10
20
30
Execution Plan
----------------------------------------------------------
Plan hash value: 596748738
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 5 (40)| 00:00:01 |
| 1 | SORT UNIQUE | | 14 | 182 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
469 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> select distinct deptno from scott.emp;
DEPTNO
----------
30
20
10
Execution Plan
----------------------------------------------------------
Plan hash value: 3709190377
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 182 | 4 (25)| 00:00:01 |
| 1 | HASH UNIQUE | | 14 | 182 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
469 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
h2.5. Sort join
SQL> select /*+ ordered use_merge(e) */ *
2 from scott.dept d, scott.emp e
3 where d.deptno=e.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 81/06/09 2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 81/11/17 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 82/01/23 1300 10
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 81/04/02 2975 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 81/12/03 3000 20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 87/05/23 1100 20
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 80/12/17 800 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 87/04/19 3000 20
30 SALES CHICAGO 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
30 SALES CHICAGO 7900 JAMES CLERK 7698 81/12/03 950 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 81/05/01 2850 30
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1407029907
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1638 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 1638 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 120 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 1218 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
72 recursive calls
0 db block gets
37 consistent gets
0 physical reads
0 redo size
1646 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select * from dba_ind_columns where table_name = 'DEPT';
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ---------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
SCOTT PK_DEPT SCOTT DEPT
DEPTNO
1 22 0 ASC
SQL> select /*+ ordered use_merge(e) index(d PK_DEPT) */ *
2 from scott.dept d, scott.emp e
3 where d.deptno=e.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 81/06/09 2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 81/11/17 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 82/01/23 1300 10
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 81/04/02 2975 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 81/12/03 3000 20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 87/05/23 1100 20
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 80/12/17 800 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 87/04/19 3000 20
30 SALES CHICAGO 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
30 SALES CHICAGO 7900 JAMES CLERK 7698 81/12/03 950 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 81/05/01 2850 30
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1638 | 807 (1)| 00:00:10 |
| 1 | MERGE JOIN | | 14 | 1638 | 807 (1)| 00:00:10 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 120 | 803 (1)| 00:00:10 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 2 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 1218 | 4 (25)| 00:00:01 | ==>OUTER 테이블인 DEPT의 DEPTNO컬럼의 인덱스 사용시 SORT가 한번만 발생확인
| 5 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="E"."DEPTNO")
filter("D"."DEPTNO"="E"."DEPTNO")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1646 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
h2.6. Window sort
SQL> select empno, ename, job, mgr, sal
2 ,avg(sal) over (partition by deptno)
3 from scott.emp;
EMPNO ENAME JOB MGR SAL AVG(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- --------- ---------- ---------- -------------------------------
7782 CLARK MANAGER 7839 2450 2916.66667
7839 KING PRESIDENT 5000 2916.66667
7934 MILLER CLERK 7782 1300 2916.66667
7566 JONES MANAGER 7839 2975 2175
7902 FORD ANALYST 7566 3000 2175
7876 ADAMS CLERK 7788 1100 2175
7369 SMITH CLERK 7902 800 2175
7788 SCOTT ANALYST 7566 3000 2175
7521 WARD SALESMAN 7698 1250 1566.66667
7844 TURNER SALESMAN 7698 1500 1566.66667
7499 ALLEN SALESMAN 7698 1600 1566.66667
7900 JAMES CLERK 7698 950 1566.66667
7698 BLAKE MANAGER 7839 2850 1566.66667
7654 MARTIN SALESMAN 7698 1250 1566.66667
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 910 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 910 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 910 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1171 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed