SQL 연산(HASH SEMI-JOIN)
구로디지털 오엔제이프로그래밍실무교육센터
세미 조인은
보통 EXISTS를 사용하는 서브쿼리의 형태로 나타나며 이러한 경우 서브 쿼리에 인덱스가 존재하지
않는다면 상당히 비효율적인데 이러한 서브 쿼리에 인덱스가 없는 경우 SEMI-JOIN이 일어나도록
유도한다면 성능의 향상을 꽤 할 수 있습니다. 즉 인덱스가 없는
EXISTS를 사용하는 쿼리라면 HASH_SJ or MERGE_SJ 힌트 구분을 이용해서
세미조인이 일어나도록 푸는 것이 좋습니다.
아래의
질의는 EMP 테이블에서 부서에 속해있는 직원들의 이름,
급여를 출력하는 예입니다.
(현시점에 DEPT TABLE의
DEPTNO는 인덱스가 존재한다고 가정하겠습니다)
오라클 11g의 경우 depttest
table에 인덱스가 없어도 HASH SEMI JOIN을 실제 수행한다. 그래서 RULE BASED OPTIMIZER로 변환 후 위의
쿼리를 실행했습니다.
-- emptest 테이블은
250만건
CREATE TABLE emptest AS SELECT ROWNUM AS empno , MOD(ROWNUM,5) AS deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐) , '홍길동'||ROWNUM AS ename --랜덤 문자5개
, '서울'||ROWNUM AS addr , MOD(ROWNUM,1000000) AS sal --랜덤문자 5개
FROM dual CONNECT BY ROWNUM <= 2500000 --250만건 만들자... -- depttest 테이블은 5건 CREATE TABLE depttest AS SELECT MOD(ROWNUM,5) AS deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐) , '부서명'||MOD(ROWNUM,5) AS dname --랜덤 문자5개 FROM dual CONNECT BY ROWNUM <= 5 --5건 만들자... -- emptest의 deptno로 인덱스를 만들자. create index idx_emptest_deptno on emptest(deptno); -- 통계정보 생성
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPTEST') exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT')
CREATE TABLE emptest AS SELECT ROWNUM AS empno , MOD(ROWNUM,5) AS deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐) , '홍길동'||ROWNUM AS ename --랜덤 문자5개
, '서울'||ROWNUM AS addr , MOD(ROWNUM,1000000) AS sal --랜덤문자 5개
FROM dual CONNECT BY ROWNUM <= 2500000 --250만건 만들자... -- depttest 테이블은 5건 CREATE TABLE depttest AS SELECT MOD(ROWNUM,5) AS deptno--5개씩 그룹핑 (0,1,2,3,4값만가짐) , '부서명'||MOD(ROWNUM,5) AS dname --랜덤 문자5개 FROM dual CONNECT BY ROWNUM <= 5 --5건 만들자... -- emptest의 deptno로 인덱스를 만들자. create index idx_emptest_deptno on emptest(deptno); -- 통계정보 생성
exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPTEST') exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT')
SQL>alter session set optimizer_mode=rule;
SQL> SELECT count(ENAME)
2 FROM EMPTEST E
3 WHERE EXISTS ( SELECT 1
4 FROM DEPTTEST
D
5 WHERE E.DEPTNO =
D.DEPTNO);
COUNT(ENAME)
------------
2500000
경 과: 00:00:01.45
Execution Plan
----------------------------------------------------------
Plan hash value: 3882039758
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS FULL| EMPTEST |
|* 4 | TABLE ACCESS FULL| DEPTTEST |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "DEPTTEST" "D"
WHERE
"D"."DEPTNO"=:B1))
4 - filter("D"."DEPTNO"=:B1)
Note
-----
- rule based optimizer used (consider using
cbo)
해시
세미조인이 일어나도록 유도한 SQL문은 다음과 같습니다.
SQL> SELECT count(ENAME)
2 FROM EMPTEST E
3 WHERE EXISTS ( SELECT 1
4 FROM DEPTTEST
D
5 WHERE E.DEPTNO =
D.DEPTNO);
COUNT(ENAME)
------------
2500000
경 과: 00:00:00.48
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time
| 0 | SELECT STATEMENT | | 1 | 20 | 4172 (2)|
| 1 | SORT AGGREGATE | | 1 | 20 |
|
|* 2 | HASH JOIN RIGHT SEMI| | 2500K| 47M| 4172 (2)|
| 3 | TABLE ACCESS FULL | DEPTTEST | 5 | 15 | 3 (0)|
| 4 | TABLE ACCESS FULL | EMPTEST | 2500K| 40M| 4156
(1)|
댓글 없음:
댓글 쓰기