각종 SORT 발생 operation 종류 정리

출처 : 친절한 SQL 튜닝

SQLP 대비로 실행계획 상에서 SORT 관련 operation이 발생하는 case를 정리하겠습니다.

 

1. 단순 집계함수만 사용시 - SORT (AGGREGATE)

 

select sum(sal),min(sal) From emp

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=4)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=56)
-----------------------------------------------------------

 

 

2. order by 사용시 - SORT (ORDER BY)

select * From emp order by sal desc

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=532)
   1    0   SORT (ORDER BY) (Cost=4 Card=14 Bytes=532)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=532)
-----------------------------------------------------------

 

3. group by 사용시 - HASH (GROUP BY) 

select deptno,sum(Sal),min(Sal) From emp 
group by deptno;

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=21)
   1    0   HASH (GROUP BY) (Cost=4 Card=3 Bytes=21)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=98)
-----------------------------------------------------------

 

4.order by groupby 함께 사용시 - SORT(GROUP BY)
select deptno,sum(Sal),min(Sal) From emp 
group by deptno
order by deptno desc

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=21)
   1    0   SORT (GROUP BY) (Cost=4 Card=3 Bytes=21)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=98)
-----------------------------------------------------------

 

5.distinct 사용시 - HASH (UNIQUE) 

select distinct deptno from emp 

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=9)
   1    0   HASH (UNIQUE) (Cost=4 Card=3 Bytes=9)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)
-----------------------------------------------------------

 

6.distinct + order by - SORT (UNIQUE) 

select distinct deptno from emp order by deptno

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=3 Bytes=9)
   1    0   SORT (UNIQUE) (Cost=4 Card=3 Bytes=9)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=42)
-----------------------------------------------------------

 

7.window함수 사용시 - WINDOW (SORT)

select empno,avg(sal) over (partition by deptno)
from emp;

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=154)
   1    0   WINDOW (SORT) (Cost=4 Card=14 Bytes=154)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=154)
-----------------------------------------------------------

 

8.union 사용 시 - union al + SORT (UNIQUE)

select job,mgr from emp where deptno = 10
union
select job,mgr from emp where deptno = 20

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=8 Bytes=120)
   1    0   SORT (UNIQUE) (Cost=8 Card=8 Bytes=120)
   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Bytes=45)
   4    2       TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=5 Bytes=75)
-----------------------------------------------------------