새로쓴 대용량 데이터베이스솔루션 1 (2011년)
Sort Merge형 세미조인
세미조인
- 조인형태가 가지는 고유한 장.단점이나 적용기준들도 거의 그대로 통용된다. 이 말은 곧 세미 조인에서도 상황에 따라 적절한 조인 형식을 적용하여야 함을 의미한다.
- 연결고리의 이상이 발생하거나 대량의 데이터를 연결해야 할 때는 세미 조인에서도 Sort Merge 형 조인이 적용 될 수 있다.
- 사원 테이블과 근태 테이블은 연결고리인 '부서코드'에서 볼때는 M:M 관계를 가지고 있지만 서브 쿼리 특성상 항상 메이쿼리의 집합은 보존되므로 물로 결과는 M:1 조인과 동일 함
준비스크립트
DROP TABLE 사원 PURGE;
CREATE TABLE 사원 AS
SELECT 사번
, 성명
, 직급
, 입사일
, 부서코드
, DECODE( 부서코드, 1, 'DB1팀', 2, 'DB2팀', 3, 'DB3팀'
, 4, '시스템1팀', 5, '시스템2팀', 6, '시스템3팀'
, 7, '경리1과', 8, '경리2과', 9, '경리3과', 10, '경리과', 11, '경리4과'
, 12, '개발1팀', 13, '개발2팀', 14, '개발3팀', 15, '개발4팀', 16, '개발5팀', 17, '개발6팀'
, 18, 'MD1팀', 19, 'MD2팀', 20, 'MD3팀', 21, 'MD4팀'
, 22, '디자인1팀', 23, '디자인2팀', 24, '디자인3팀', 25, '디자인4팀'
, 26, '멀티미디어1팀', 27, '멀티미디어2팀', 28, '멀티미디어3팀'
, 29, '모바일1팀'
, 30, '웹모바일팀'
, 31, '마케팅팀'
, 32, '기획팀' ) 부서
FROM (SELECT LEVEL AS 사번
, '아무개'||LEVEL AS 성명
, TRUNC( dbms_random.value( 1,7 ) ) 직급
, TRUNC( SYSDATE ) - 100 + CEIL( LEVEL/ 10000 ) 입사일
, TRUNC( dbms_random.value( 1,32 ) ) 부서코드
FROM DUAL
CONNECT BY LEVEL <= 1000000
)
CREATE UNIQUE INDEX 사원_PK ON 사원 ( 사번 )
--CREATE INDEX 부서_INDEX_01 ON 사원 ( 부서코드, 직급 );
CREATE TABLE 근태 AS
SELECT A.사번
, B.부서코드
, A.근태유형
, A.일자
FROM (SELECT TRUNC( dbms_random.value( 1,1000000 ) ) 사번
, DECODE( A.JOIN_C, 1,'무단결근',2,'조퇴', 3,'지각', 4,'지각', 5,'지각', 6, '연차', 7, '연차', 8,'휴가', 9,'휴가',10, '지각') 근태유형
, A.일자
FROM (SELECT TRUNC( dbms_random.value( 1,10 ) ) JOIN_C --
, TO_CHAR( TO_DATE( '20050101', 'YYYYMMDD') + LEVEL -1, 'YYYYMMDD' ) 일자
FROM DUAL
CONNECT BY LEVEL <= 365--TO_DATE( '20050630', 'YYYYMMDD') - TO_DATE( '20050501', 'YYYYMMDD')
) A
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 6 ) B
WHERE A.JOIN_C >= B.LV
) A
, 사원 B
WHERE A.사번 = B.사번
CREATE INDEX 유형_일자_INX ON 근태 ( 근태유형, 일자 )
SQL> SELECT COUNT(*) FROM 근태
2 ;
COUNT(*)
----------
1574
p. 591 테스트
SQL> SELECT /*+ gather_plan_statistics QB_NAME( MAIN ) LEADING( B@SUB ) UNNEST( @SUB ) USE_MERGE( B@SUB A@MAIN ) */ COUNT(*)
2 FROM 사원 A
3 WHERE 부서코드 IN ( SELECT /*+ QB_NAME( SUB ) */ 부서코드
4 FROM 근태 B
5 WHERE 일자 BETWEEN '20050601' AND '20050612'
6 AND 근태유형 = '지각' )
7 AND 직급 >= 3;
COUNT(*)
----------
365514
SQL> @XPLAN
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.43 | 6336 | | | |
| 2 | MERGE JOIN | | 1 | 351K| 365K|00:00:02.44 | 6336 | | | |
| 3 | SORT JOIN | | 1 | 10 | 17 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 4 | SORT UNIQUE | | 1 | 19 | 17 |00:00:00.01 | 3 | 9216 | 9216 | 8192 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| 근태 | 1 | 19 | 19 |00:00:00.01 | 3 | | | |
|* 6 | INDEX RANGE SCAN | 유형_일자_| 1 | 19 | 19 |00:00:00.01 | 2 | | | |
|* 7 | SORT JOIN | | 17 | 573K| 365K|00:00:01.03 | 6333 | 13M| 1381K| 11M (0)|
|* 8 | TABLE ACCESS FULL | 사원 | 1 | 573K| 666K|00:00:01.33 | 6333 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("근태유형"='지각' AND "일자">='20050601' AND "일자"<='20050612')
7 - access("부서코드"="부서코드")
filter("부서코드"="부서코드")
8 - filter("직급">=3)
p. 592 책처럼 실행계획으로 나오려면...
SQL> SELECT /*+ gather_plan_statistics LEADING( A ) USE_MERGE( B ) */ COUNT(*)
2 FROM 사원 A
3 , (SELECT DISTINCT 부서코드
4 FROM 근태 B
5 WHERE 일자 BETWEEN '20050601' AND '20050612'
6 AND 근태유형 = '지각' ) B
7 WHERE A.부서코드 = B.부서코드
8 AND 직급 >= 3;
COUNT(*)
----------
365514
SQL> @XPLAN
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:06.96 | 6336 | | | |
| 2 | MERGE JOIN | | 1 | 351K| 365K|00:00:05.50 | 6336 | | | |
| 3 | SORT JOIN | | 1 | 573K| 666K|00:00:01.72 | 6333 | 13M| 1381K| 11M (0)|
|* 4 | TABLE ACCESS FULL | 사원 | 1 | 573K| 666K|00:00:02.00 | 6333 | | | |
|* 5 | SORT JOIN | | 666K| 19 | 365K|00:00:04.28 | 3 | 73728 | 73728 | |
| 6 | VIEW | | 1 | 19 | 17 |00:00:00.01 | 3 | | | |
| 7 | HASH UNIQUE | | 1 | 19 | 17 |00:00:00.01 | 3 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| 근태 | 1 | 19 | 19 |00:00:00.01 | 3 | | | |
|* 9 | INDEX RANGE SCAN | 유형_일자_| 1 | 19 | 19 |00:00:00.01 | 2 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("직급">=3)
5 - access("A"."부서코드"="B"."부서코드")
filter("A"."부서코드"="B"."부서코드")
9 - access("근태유형"='지각' AND "일자">='20050601' AND "일자"<='20050612')
- HASH UNIQUE ( SORT UNIQUE ) : '1' 집합을 만든다 ( 메인 쿼리 보존 )
- Sort Merge형식으로 수행 될 때는 다른 집합에서 수해오딘 결과를 받아서 처리할 수 없으므로 독자적으로 처리범위를 충분히 줄일 수 있을 때 효과적이며
- 랜덤 액세스가 줄어들기 때문에 경우에 따라서는 매우 효과적일 수도 있다는 장점은 일반적인 조인 시와 동일하다.( ㅡㅡ )
- 예를 들어 앞서 예를 든 SQL의 서브쿼리에 GROUP BY가 있었다고 가정해보자. 그 결과는 이미 가공된 결과의 집합이므로 인덱스를 사용할 수 없다
- 그러나 GROUP BY에 의해 집합의 크기가 줄어 들었으므로 조인의 량은 감소한다. 이런 경우에는 Sort Merge형 세미 조인이 나름대로 가치가 있다.
- 물론 이런 경우에도 서브 쿼리를 인라인뷰로 만들어 일반 조인으로 연결하는 것도 내용적으로 거의 동일하다.
- 연결고리의 조건 조건에 'NOT'을 사용한 경우에도 이러한 형태의 연결이 나타날 수 있다.
- 여기에 대한 상세한 내용은 '부정형( ANTI ) 세미조인( Page 599 ~ 605 )에서 설명될 것이다.
- 참고 사이트 : http://scidb.tistory.com/search/SORT
Sort Merge Join에 대한 오만과 편견
- 양쪽 집합이 Full Table Scan을 사용하면 조인순서에 상관없이 일량이 동일하므로 처리시간도 동일하다.
- 조인순서에 상관없이 Sort량은 동일하다
- 부분범위처리가 안된다.
- Full Scan이 발생하면 인덱스를 사용할 수 없으므로 항상 Sort 작업을 동반한다.
- Sort Merge Join 대신 Catesian Merge Join이 나오면 조인 조건이 빠진 악성 SQL이다.
- 조인 컬럼 기준에 Sort되므로 Order by절과 조인 컬럼이 일치해야만 Sort가 발생하지 않는다.
왜 뜬꿈없이 오만과 편견이냐? 책에서 Sort Merge 조인으로 실행계획이 수립 되어서 테스트 중 결과가 도출되어 겸사겸사 설명까지 첨부.으흠.
2. 조인순서에 상관없이 처리시간과 Sort량 동일 할까? ( 맛베기.. 시간이 없어요 남음할게요.. ㅠ )
p. 592 책처럼 실행계획으로..LEADING( 사원 )
SQL> SELECT /*+ gather_plan_statistics LEADING( A ) USE_MERGE( B ) */ COUNT(*)
2 FROM 사원 A
3 , (SELECT DISTINCT 부서코드
4 FROM 근태 B
5 WHERE 일자 BETWEEN '20050601' AND '20050612'
6 AND 근태유형 = '지각' ) B
7 WHERE A.부서코드 = B.부서코드
8 AND 직급 >= 3;
COUNT(*)
----------
365514
SQL> @XPLAN
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:06.92 | 6336 | | | |
| 2 | MERGE JOIN | | 1 | 351K| 365K|00:00:05.42 | 6336 | | | |
| 3 | SORT JOIN | | 1 | 573K| 666K|00:00:01.63 | 6333 | 13M| 1381K| 11M (0)|
|* 4 | TABLE ACCESS FULL | 사원 | 1 | 573K| 666K|00:00:01.33 | 6333 | | | |
|* 5 | SORT JOIN | | 666K| 19 | 365K|00:00:04.33 | 3 | 73728 | 73728 | |
| 6 | VIEW | | 1 | 19 | 17 |00:00:00.01 | 3 | | | |
| 7 | HASH UNIQUE | | 1 | 19 | 17 |00:00:00.01 | 3 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| 근태 | 1 | 19 | 19 |00:00:00.01 | 3 | | | |
|* 9 | INDEX RANGE SCAN | 유형_일자_| 1 | 19 | 19 |00:00:00.01 | 2 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("직급">=3)
5 - access("A"."부서코드"="B"."부서코드")
filter("A"."부서코드"="B"."부서코드")
9 - access("근태유형"='지각' AND "일자">='20050601' AND "일자"<='20050612')
p. 592 책처럼 실행계획으로..LEADING( 근태 )
SQL> SELECT /*+ gather_plan_statistics LEADING( B ) USE_MERGE( A B ) */ COUNT(*)
2 FROM 사원 A
3 , (SELECT DISTINCT 부서코드
4 FROM 근태 B
5 WHERE 일자 BETWEEN '20050601' AND '20050612'
6 AND 근태유형 = '지각' ) B
7 WHERE A.부서코드 = B.부서코드
8 AND 직급 >= 3;
COUNT(*)
----------
365514
SQL> @XPLAN
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.41 | 6336 | | | |
| 2 | MERGE JOIN | | 1 | 351K| 365K|00:00:02.44 | 6336 | | | |
| 3 | SORT JOIN | | 1 | 19 | 17 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 4 | VIEW | | 1 | 19 | 17 |00:00:00.01 | 3 | | | |
| 5 | HASH UNIQUE | | 1 | 19 | 17 |00:00:00.01 | 3 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| 근태 | 1 | 19 | 19 |00:00:00.01 | 3 | | | |
|* 7 | INDEX RANGE SCAN | 유형_일자_| 1 | 19 | 19 |00:00:00.01 | 2 | | | |
|* 8 | SORT JOIN | | 17 | 573K| 365K|00:00:01.02 | 6333 | 13M| 1381K| 11M (0)|
|* 9 | TABLE ACCESS FULL | 사원 | 1 | 573K| 666K|00:00:01.33 | 6333 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("근태유형"='지각' AND "일자">='20050601' AND "일자"<='20050612')
8 - access("A"."부서코드"="B"."부서코드")
filter("A"."부서코드"="B"."부서코드")
9 - filter("직급">=3)
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4453
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.