\- 전체 로우를 대상으로 집계를 수행할떄 발생 함. ('sort'라는 표현을 쓰지만 실제 소트는 일어나지 않는다.)
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> 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
– sort group by는 소팅 알고리즘에 의해 그룹별 집계함수 생성시 발생.
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
h3.GROUP BY 방법과 정렬 방법
| 중요 sort group by의 의미는 소팅알고리즘을 사용하여 값을 집계할뿐 결과의 정렬을 의미하지 않는다. 바꿔말해 정렬된 group by의 결과를 얻고자 한다면 실핼계획에 sort group by라고 표시되어도 order by를 반드시 명시해야한다. |
|---|
set autot on
alter system set "_gby_hash_aggregation_enabled" = true;
select ksppinm, ksppstvl
from x$ksppi x, x$ksppcv y
where x.indx = y.indx
and translate(ksppinm,'_','#') like '_gby_hash_aggregation_enabled%';
alter system set "_gby_hash_aggregation_enabled" = false;
select owner,object_type,count
from all_objects
group by owner,object_type;
TEST2. 8i에서 인덱스 생성후 인덱스를 이용하여 GROUP BY 시도시 정렬되서 보일까?
SQL> select /*+ index(emp emp_idx) */ deptno,job,avg(sal)
2 from scott.emp
3 where deptno is not null
4 group by deptno, job;
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 ANALYST 3000
10 CLERK 1300
20 CLERK 950
30 CLERK 950
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
10 PRESIDENT 5000
30 SALESMAN 1400
9 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1347804211
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 448 | 2 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 14 | 448 | 2 (0)| 00:00:01 | ====> SORT GROUP BY NOSORT
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 448 | 2 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN | EMP_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
697 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
TEST3. ORDER BY를 사용하지 않았을때 10gR2에서 SORT GROUP BY가 나타나는 경우
SQL> alter system set "_gby_hash_aggregation_enabled" = true;
시스템이 변경되었습니다.
SQL> select ksppinm, ksppstvl
2 from x$ksppi x, x$ksppcv y
3 where x.indx = y.indx
4 and translate(ksppinm,'_','#') like '_gby_hash_aggregation_enabled%';
KSPPINM
--------------------------------------------------------------------------------
KSPPSTVL
---------------------------------------------------------------------------------
------------------------------------------------------------
_gby_hash_aggregation_enabled
TRUE
SQL> select deptno, job, count(distinct ename)
2 from scott.emp
3 group by deptno,job;
DEPTNO JOB COUNT(DISTINCTENAME)
---------- --------- --------------------
10 CLERK 1
10 MANAGER 1
10 PRESIDENT 1
20 CLERK 2
20 ANALYST 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
9 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 14 | 364 | 4 (25)| 00:00:01 | ==>10g임에도 sort group by 진행됨을 확인.
| 2 | TABLE ACCESS FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
27 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
721 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
distinct count 연산을 하려면 deptno, job, ename으로 정렬하고서 deptno,job별로 Unique 한 ename 개수를 세야 한다.
그런데 해싱 알고리즘은 정렬을 하지 않는 구조여서 deptno, job이 같아도 ename에 따라 서로 다른 해시 버킷으로 흩어지므로 distinct count 연산에 쓰일 수 없다.
따라서 오라클은 distinct count를 만났을 땐 항상 sort group by 방식으로 수행한다.
h2.(4)Sort unique
– Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인텍스가 없다면， 그리고 세미 조인으로 수행되지도 않는다면 메인 쿼리와 조인되기 전에 sort unique오퍼레이션이 먼저 수행된다.
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
– 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
– 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