대용량 데이터베이스솔루션 2 (2007년)
- 3.1 방사형 조인의 문제점
- 3.2 인라인뷰를 이용한 해결
- 3.3 GROUP BY된 인라인뷰의 조인 문제점
- 3.4 GROUP BY된 인라인뷰의 NESTED LOOPS 조인
- 3.5 방사형 조인의 기타 해결방법 (단계적인 GROUP BY 결과의 조인)
3.1 방사형 조인의 문제점
3.1.1 예시테이블(3-14 페이지 참조)
- 부서:DETP
- 사원:EMP
- 가족:FAMILY
- 급여:SAL
- 부서:사원=1:M
- 사원:가족=1:M
- 사원:급여=1:M
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT EMP_PK PRIMARY KEY ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 20);
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT DEPT_PK PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE SAL(
EMPNO NUMBER(4),
YYMMDD VARCHAR2(8),
SALARY NUMBER,
SUDANG NUMBER,
CONSTRAINT SAL_PK PRIMARY KEY (EMPNO,YYMMDD)
)
INSERT INTO SAL VALUES(7369,'20060101',1000,500);
INSERT INTO SAL VALUES(7369,'20060201',1000,500);
INSERT INTO SAL VALUES(7369,'20060301',1000,500);
INSERT INTO SAL VALUES(7369,'20060401',1000,500);
INSERT INTO SAL VALUES(7499,'20060101',1000,500);
INSERT INTO SAL VALUES(7499,'20060201',1000,500);
INSERT INTO SAL VALUES(7499,'20060301',1000,500);
INSERT INTO SAL VALUES(7902,'20060301',1000,500);
INSERT INTO SAL VALUES(7902,'20060401',1000,500);
INSERT INTO SAL VALUES(7934,'20060401',1000,500);
CREATE TABLE FAMILY(
EMPNO NUMBER(4),
SEQ NUMBER,
RELASHION VARCHAR2(5),
BUYANG CHAR(1),
CONSTRAINT FAMILY_PK PRIMARY KEY (EMPNO,SEQ)
)
INSERT INTO FAMILY VALUES(7369,1,'부','Y');
INSERT INTO FAMILY VALUES(7369,2,'모','Y');
INSERT INTO FAMILY VALUES(7369,3,'형','N');
INSERT INTO FAMILY VALUES(7369,4,'누나','N');
INSERT INTO FAMILY VALUES(7369,5,'동생','N');
INSERT INTO FAMILY VALUES(7499,1,'부','Y');
INSERT INTO FAMILY VALUES(7499,2,'형','Y');
INSERT INTO FAMILY VALUES(7902,1,'모','N');
INSERT INTO FAMILY VALUES(7902,2,'형','N');
3.1.2 어떤 부서에 속한 사원들을 찾아 각 사원별로 3개월간의 평균 급여와 부양가족 수를 구한 후 평균급여*부양가족수*0.12 수식으로 가족수당을 구하라.
SELECT
부서명
,B.사원번호
,AVG(SUBSTR(DISTINCT D.ROWID||급여총액,19,15))*COUNT(DISTINCT C.ROWID))*0.12
FROM 부서 A,사원 B,급여 C,가족 D
WHERE A.부서코드=:DEPT_CD
AND A.부서코드=B.부서코드
AND B.사원번호=C.사원번호( + )
AND B.사원번호=D.사원번호( + )
AND C.부양여부( + )='Y'
AND D.년월( + ) BETWEEN TO_CHAR(TO_DATE(:INDATE||'10','YYYYMMDD')-90,'YYYYMM') AND :INDATE
예제의 쿼리처림 1:M:M의 관계를 가지는 테이블들을 단순하게 조인시 1*M쪽 로우수*M쪽 로우수 만큼의 데이타 복제가 발생하여 옳바르지 못한 값이 출력된다
3.2 인라인뷰를 이용한 해결
SELECT
부서명
,B.사원번호
,AVG_AMT*DECODE(B.직무,'A1',0.12,0.11)*가족수
FROM 부서 A,사원 B
,(
SELECT 사원번호,COUNT(*) 가족수
FROM 가족
WHERE 부양여부='Y'
GROUP BY 사원번호
) C
,(
SELECT 사원번호,AVG(급여총액) AVG_AMT
FROM 급여
WHERE 년월( + ) BETWEEN TO_CHAR(TO_DATE(:INDATE||'10','YYYYMMDD')-90,'YYYYMM') AND :INDATE
) D
WHERE A.부서코드=:DEPT_CD
AND A.부서코드=B.부서코드
AND B.사원번호=C.사원번호( + )
AND B.사원번호=D.사원번호( + )
M에 해당하는 가족,급여 테이블을 사원번호로 GROUP BY하여 사원테이블과 1:1의 관계를 가지게 함으로써 데이타 복제에 따른 오류가 발생하지 않는다.
3.3 GROUP BY된 인라인뷰의 조인 문제점
SELECT
A.DNAME,B.EMPNO,CNT,AMT
FROM DEPT A,EMP B
,(
SELECT EMPNO,COUNT(*) CNT
FROM FAMILY
WHERE BUYANG='Y'
GROUP BY EMPNO
) C
,(
SELECT EMPNO,SUM(SALARY) AMT
FROM SAL
WHERE YYMMDD BETWEEN '20060101' AND '20060331'
GROUP BY EMPNO
) D
WHERE A.DEPTNO=20
AND A.DEPTNO=B.DEPTNO
AND B.EMPNO=C.EMPNO(+)
AND B.EMPNO=D.EMPNO(+)
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE
MERGE JOIN OUTER
MERGE JOIN OUTER
SORT JOIN
MERGE JOIN
TABLE ACCESS BY INDEX ROWID SCOTT.DEPT
INDEX UNIQUE SCAN SCOTT.DEPT_PK
FILTER
TABLE ACCESS FULL SCOTT.EMP
SORT JOIN
VIEW
SORT GROUP BY
TABLE ACCESS FULL SCOTT.SAL
SORT JOIN
VIEW
SORT GROUP BY
TABLE ACCESS FULL SCOTT.FAMILY
1) 처리방법:3-20 페이지 참조
2) 문제점
- 사원테이블에서 DEPTNO=20인 사원은 6명에 불과하다. 그러나 SAL,FAMILY테이블의 처리를 보면 TABLE ACCESS FULL로써 모든 사원에 대해 엑세스하고 있슴을 알 수 있다.
- 또한 부서+사원의 조인결과와 SAL,FAMILY 인라인뷰간에 MERGE가 발생하여 정렬이 일어난 부담까지 생겼다.
3) 결국 불필요한 엑세스가 많이 일어났다는 증거이며 이는 부서와 사원의 조인에 의한 결과집합의 사원번호가 SAL,FAMILY의 인라인뷰에 상수값으로 주어지지 못하여 처리범위를 줄여주지 못했슴을 의미한다.
3.4 GROUP BY된 인라인뷰의 NESTED LOOPS 조인
3.4.1 3.3에서 제기된 문제점의 해결 (3-28 페이지 참조)
SELECT
A.DNAME,B.EMPNO,CNT,AMT
FROM DEPT A,EMP B
,(
SELECT EMPNO,COUNT(*) CNT
FROM FAMILY
WHERE BUYANG='Y'
GROUP BY EMPNO
) C
,(
SELECT EMPNO,SUM(SALARY) AMT
FROM SAL
WHERE YYMMDD BETWEEN '20060101' AND '20060331'
GROUP BY EMPNO
) D
WHERE A.DEPTNO=20
AND B.EMPNO > 0 --추가
AND A.DEPTNO=B.DEPTNO
AND B.EMPNO=C.EMPNO(+)
AND B.EMPNO=D.EMPNO(+)
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 5 9
HASH JOIN OUTER 5 340 9
HASH JOIN OUTER 5 210 6
NESTED LOOPS 5 80 3
TABLE ACCESS BY INDEX ROWID SCOTT.DEPT 1 11 1
INDEX UNIQUE SCAN SCOTT.DEPT_PK 4
TABLE ACCESS BY INDEX ROWID SCOTT.EMP 5 25 2
INDEX RANGE SCAN SCOTT.EMP_PK 14 1
VIEW 3 78 2
SORT GROUP BY 3 12 2
TABLE ACCESS BY INDEX ROWID SCOTT.FAMILY 5 20 2
INDEX RANGE SCAN SCOTT.FAMILY_PK 9 1
VIEW 4 104 2
SORT GROUP BY 4 52 2
TABLE ACCESS BY INDEX ROWID SCOTT.SAL 9 117 2
INDEX RANGE SCAN SCOTT.SAL_PK 9 1
- 1) 만일 사원번호가 변수로 주어진다면 AND B.EMPNO LIKE :EMPNO||'%' 조건문을 추가함으로써 AND B.EMPNO=C.EMPNO( + ) ,AND B.EMPNO=D.EMPNO( + ) 조건을 매개로 하여 EMPNO조건이 SAL 인라인뷰,FAMILY 인라인뷰에 조건으로 파고 들어간다.
- 2) 그 결과 실행계획에서 보는 것처럼 TABLE FULL SCAN 이 INDEX RANGE SCAN로 변화하게 된다. 이는 부서 + 사원 테이블의 조인 결과에서 사원번호가 각 인라인뷰에 공급되어 불필요한 데이타 엑세스를 없앤 것으로 볼 수 있다( 여기서는 실행계획을 얻기 위해 B.EMPNO > 0 이라는 조건을 억지로 붙였다 )
- 3) 그러나 ORDERED, USE_NL(A B C D) LEADING(A) 등 힌트를 사용해도 NESTED LOOPS로 실행계획이 풀리지는 않았다.
3.4.2 보충설명
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
NESTED LOOPS
TABLE ACCESS (BY INDEX ROWID) OF 'TAB1'
INDEX (RANGE SCAN) OF 'INDEX_1'
VIEW
SORT (GROUP BY)
TABLE ACCESS (FULL) OF 'TAB2'
1) 처리절차:3-26 페이지 참조
2) NESTED LOOPS 조인의 나중에 위치한 집합이라고 해서 반드시 전체를 반복 수행하는 것은 아니다.
3) 즉 TAB1에서 10개의 로우가 추출되었을 경우 각 로우마다 TAB2에 대해 테이블 풀 스캔 후 GROUP BY를 하고 난 후 그 결과와 조인하는 것이 아니라
TAB2의 풀 스캔한 후 GROUP BY한 결과를 내부적으로 저장한 후 이 결과와 10개의 로우에 대해 조인을 한다.
4) 따라서 비록 TAB2가 테이블 풀 스캔이지만 테이블 크기가 크지 않거나 자체적인 조건에 의해 처리범위를 줄일 수 있다면 충분히 활용가능한 방법이다.
3.5 방사형 조인의 기타 해결방법 (단계적인 GROUP BY 결과의 조인)
SELECT
MIN(부서명)
,X.사원번호
,AVG(급여총액)*MIN(DECODE(직무,'A1',0.12,0.11)*가족수
FROM
(
SELECT
B.사원번호
,MIN(부서명) 부서명
,MIN(B.직무) 직무
,COUNT(C.사원번호) 가족수
FROM 부서 A,사원 B,가족 C
WHERE B.부서코드=A.부서코드
AND C.사원번호 ( + )=B.사원번호
AND A.부서코드= : DEPT_CD
AND C.부양여부='Y'
GROUP BY B.사원번호
) X,급여 Y
WHERE Y.사원번호 ( + )=X.사원번호
AND Y.년월( + ) BETWEEN TO_CHAR(TO_DATE(:INDATE||'10','YYYYMMDD')-90,'YYYYMM') AND :INDATE
GROUP BY X.사원번호
- 1) 부서,사원,가족 테이블을 조인한 후 사원번호로 GROUP BY를 하여 사원번호에 대해 1로 만든다.
- 2) 이 결과를 급여테이블과 조인을 하는데 급여 수만큼 복제가 되므로 다시 사원번호로 GROUP BY를 한다.
- 3) 결국 원하는 사원만을 엑세스하는 형태가 되어 GROUP BY된 인라인뷰의 조인 문제점으로 나타난 불필요한 엑세스가 줄어들게 된다.
- 4) 그러나 만일 특정 부서가 아닌 사원 전체를 대상으로 한다면 위의 쿼리는 모든 사원에 대해 랜덤 엑세스가 발생하므로 오히려 3.2에서 보인 쿼리가 더 효율적일 수 있다.
- 5) 이외에 M쪽 집합들을 사용자 저장형 함수를 사용하여 1집합으로 만드는 방법이 있다.
"구루비 데이터베이스 스터디모임" 에서 2007년에 "대용량 데이터베이스 솔루션 2" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/2489
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.