새로쓴 대용량 데이터베이스솔루션 1 (2011년)
연산방식에 따른 실행계획
실행계획은 SQL에 사용한 연산방식에 따라서 다양하게 나타난다.
- In-List 탐침(Iterator) 실행계획
- 연쇄(Concatenation) 실행계획
- 원격(Remote) 실행계획
- 정렬처리(Sort Operation) 실행계획
- 집합처리(Set Operation) 실행계획
- COUNT(STOPKEY) 실행계획
In-List 탐침 실행계획 - INLIST ITERATOR
- INLIST ITERATOR 아래에 있는 처리를 IN조건에 나열된 값만큼 반복수행한다.
- 'BETWEEN'은 선분을 의미하나, 'IN'은 여러개의 점을 의미한다.
- 선분의 개념은 Range Scan을 하게 되지만, 점의 개념은 '='을 사용할 수 있다.
- 옵티마이저는 IN연산을 OR형태로 변경한 후 실행계획을 수립하므로, IN대신 OR을 사용해도 실행계획은 동일하다.
/* IN절을 사용한 SQL */
SELECT * FROM emp
WHERE deptno IN ('10', '20');
/* OR을 사용한 SQL */
SELECT * FROM emp
WHERE deptno ='10' OR deptno = '20';
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 333 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 9 | 333 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 9 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
'IN'을 사용했는데도 INLIST ITERATOR가 나타나지 않는 경우
- IN 절의 값이 하나만 있는 경우
- 처리주관 인덱스에 적용된 경우가 아니어서 단지 체크기능만 담당하는 경우
- 인덱스 구성에서 IN을 사용한 컬럼앞에 위치한 컬럼이 모두 '='로 사용되지 않았을 때는 경우에 따라 다름
- IN 조건이 연속으로 나타났을때 는 경우에 따라 다름
- 결합된 컬럼의 개수에도 영향을 받음
- IN조건에 사용된 값이 상수, 변수, 서브쿼리일 때도 달라짐
- INDEX(table_alias index_name)힌트를 적용하여 IN조건을 사용한 컬럼이 속한 인덱스를 사용하도록 할수 있으나, 적용여부는 옵티마이저가 결정한다.
연쇄 실행계획 - CONCATENATION
- 'OR'로 연결된 서로 다른 컬럼을 사용한 조건을 별도의 실행단위로 분리하여 각각의 최적의 액세스 경로를 수립하여 이를 연결하는 실행계획
- 'OR'조건이 처리주관 조건의 역할을 하는 경우에만 그렇게 실행되며, 아닌 경우 단순히 체크조건으로만 사용된다.
- USE_CONCAT힌트로 유도가능, NO_EXPAND힌트로 적용 불가능하게 할 수 있음.
- 'OR'조건은 상황에 따라사 CONCATENATION이 유리할 수도 있고 불리할 수도 있으므로 실행계획을 확인해보고, 함부로 힌트를 적용하지 않는것이 바람직 하다.
SELECT *
FROM emp
WHERE job = 'SALES'
OR sal = 3000;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 148 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 4 | 148 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
USE_CONCAT 힌트를 사용하여 유도
SELECT /*+ USE_CONCAT */ *
FROM emp
WHERE job = 'SALES'
OR sal = 3000;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 148 | 4 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 111 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_IDX | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
적용하지 않는 것이 바람직한 경우
- 조인의 연결고리가 'OR"조건을 가질 때 조인의 상대방이 넓은 처리범위를 가질 때
- 동일 컬럼의 'OR'조건 : INLIST ITERATOR가 유리
- 보다 효율적으로 처리범위를 줄일 수 있는 다른 액세스 경로가 있을때
- 'OR'조건들 중에서 너무 넓은 처리범위를 가진 것들이 존재할 때
원격 실행계획 - REMOTE
- 다른 데이터베이서 테이블을 DB Link로 엑세스하는 형태.
- 원격 테이블의 상세한 실행계획은 나타나지 않으므로, 어떤 방법으로 액세스했는지 알 수 없다.
- 원격테이블이 실행계획에 부담이 많은 내측루프에서 수행되면 큰 부담이 된다.
- 원격테이블 액세스는 랜덤이 아닌 범위처리에는 부담이 적으므로 이와 같은 경우에는 Sort Merge나 해쉬조인으로 나타나는 경우가 많음.
- 논리적으로 가장 이상적인 방법은, 먼저 원격에서 두 테이블을 조인하고 그 결과를 로컬에서 받는 방법이다. 이렇게 처리되도록 하려면, 원격에 미리 조인된 뷰를 만든다.
- 항상 실행계획을 확인하는 습관을 가진다.
정렬처리 실행계획-SORT(UNIQUE), SORT(AGGREGATE), SORT(GROUP BY)
- 엑세스된 데이터는 사용자의 요구를 충족시키기 위해 다양한 가공을 하게 되며, 이러한 가공에는 상당부분 정렬이 필요한 경우가 많다.
- 정렬형태별로 처리방법에 차이가 있으며, 발생하는 부하의 정도도 다르다.
SORT(UNIQUE)
- DISTINCT함수를 사용했을 때
- 서브쿼리에서 제공자 역할을 할 때
SORT(AGGREGATE)
- GROUP BY를 하지 않은 상태로, 전체 대상에 대해 그룹함수로 계산할 때
SELECT SUM(sal)
FROM emp
WHERE deptno = 10;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 35 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SORT(GROUP BY)
- GROUP BY를 사용하여 여러개의 다른 그룹으로 집결을 수행할 때 발생
- 그룹의 개수가 많을수록 부담이 커짐. 이를 해결하기 위해 HASH(GROUP BY)
SORT(GROUP BY NOSORT)
- GROUP BY에 나열된 컬럼이 처리주관 인덱스의 선행컬럼과 동일하여 추가적인 정렬작업이 필요없는 경우 나타남.
SELECT deptno, SUM(sal)
FROM emp
WHERE deptno >10
GROUP BY deptno;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 2 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 2 | 14 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 9 | 63 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 9 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
집합처리 실행계획
합집합(Union, Union All) 실행계획
- UNION은 최소 공배수 집합을 구해야 하므로, 추가로 SORT(UNIQUE)를 수행해야 한다.
SELECT empno, ename
FROM emp WHERE mgr = 7698
UNION
SELECT empno, ename
FROM emp WHERE deptno = 10;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 93 | 7 (58)| 00:00:01 |
| 1 | SORT UNIQUE | | 7 | 93 | 7 (58)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | EMP | 2 | 28 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 65 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
- UNION ALL은 그런 처리가 필요하지 않다.
SELECT empno, ename
FROM emp WHERE deptno = 30
UNION
SELECT empno, ename
FROM emp WHERE deptno = 10;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 130 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 65 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 65 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
교집합(Intersection) 실행계획
- 교집합은 양쪽집합 모두에 속하는 공통집합의 의미한다.
- 먼저 각각의 집합에서 유일한 집합을 구해야 한다.
- Sort Merge조인과 유사한 방법을 사용하여 양쪽집합을 유일하게 정렬한 다음 머지한다.
SELECT empno, ename
FROM emp WHERE mgr = 7698
INTERSECT
SELECT empno, ename
FROM emp WHERE deptno = 10;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 93 | 7 (58)| 00:00:01 |
| 1 | INTERSECTION | | | | | |
| 2 | SORT UNIQUE | | 2 | 28 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 2 | 28 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 65 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 65 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
차집합(MINUS) 실행계획
- 머지작업과 유사한 형태로 처리되며, 어느 한쪽을 기준으로 다른 쪽에 존재하면 제거하는 형식으로 처리된다.
- SELECT절에 나열된 컬럼이 테이블의 기본키였다면 논리적으로 유일성이 보장되나, SORT(UNIQUE)은 발생된다.
- WHERE절에 부여된 조건이 기본키를 =로 엑세스한 경우는 SORT(UNIQUE)이 발생되지 않는다.
SELECT empno, ename
FROM emp WHERE mgr = 7698
MINUS
SELECT empno, ename
FROM emp WHERE deptno = 10;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 93 | 7 (58)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 2 | 28 | 4 (25)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 2 | 28 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 5 | 65 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 65 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
COUNT(STOPKEY) 실행계획
- 쿼리의 조건절에 ROWNUM을 사용했을 때 나타난다.
- 이 컬럼은 가상컬럼으로 ROWNUM을 잘 활용하면 내부적인 진행단계를 제어할 수도 있다.
SELECT * from emp
WHERE sal>1000
AND ROWNUM < 5;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 148 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 222 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_SAL_IDX | 13 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4449
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.