'outer join'에 해당되는 글 3건

  1. 2009.03.02 Hash Join Right (Semi/Anti/Outer) 의 용도 17
  2. 2009.02.23 Full Outer Join 의 비밀 5
  3. 2008.04.07 Outer Join 의 재조명 2

Hash Join Right (Semi/Anti/Outer) 의 용도

Oracle/Data Join Method 2009. 3. 2. 22:51
<2009.03.11 : 아래 내용중의 Right 의 의미는 Oracle 10g Performance Tuning Guide 19-21 에 나온대로 "올바른" 이란 뜻이 아니라 "Left 혹은 Right Outer Join 시의 Right" 의 의미로 바꿉니다.
하지만 이글의 핵심인 "이전버젼까지는 항상 후행집합으로 되던것이 10g 부터는 선행집합이 될수 있다." 는 바뀐것이 없습니다. >
 
오늘은 Hash Join Right  (Semi/Anti/Outer) 의 용도에 대하여 알아보려한다.
Oracle 10g 부터 Hash Join 은 서서히 변화하기 시작하였다.
특히 Hash Join Right  (Semi/Anti/Outer) 기능을 사용하여 대용량 집합의 Join 시 획기적인 성능향상을 이루었다.
Hash Join 에서 Right 옵션이 붙음으로서 획기적인 성능향상을 이루었다는 이유는 무엇일까?
Semi/Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 데이터를 체크하는 방식이다.
따라서 Semi/Anti Join 의 경우 서브쿼리는 항상 후행집합이 될수 밖에 없다.
Hash Outer Join 의 경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행집합이 될수 밖에 없었다.
하지만 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다.
이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다.
9i 까지 Hash Join (Semi/Anti/Outer)의 경우에 눈물을 머금고 대량의 집합을 선행처리할수 밖에 없었지만 이제는 자유롭게 선행집합을 선택할수 있는것이다.
국내외 튜닝책을 막론하고 이막강한 기능에 대하여 제대로 다루는 것을 본적이 없다.
왜냐하면 초대용량 DB(VLDB)에서 Join Method 를 튜닝해본 사람만이 이 기능이 얼마나 중요한지 피부로 느낄수 있기 때문이다.
아래의 스크립트를 보자.
환경 : 10.2.0.4

1.테이블을 생성하고 Aanlyze 를 한다.

1.테이블 생성
CREATE TABLE BIG_EMP AS
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO
  FROM EMP A,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE BIG_EMP
   ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade => true); 


2.Hash Semi Join 을 유도한다.

select a.empno, a.sal
  from   big_emp a
  where  exists (select /*+ use_hash(b) */
                                  b.deptno
                          from  dept b
                       where  b.deptno = a.deptno
                      ) ;

--------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts |Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |    4  (25)|  26000 |00:00:00.06 |     176 |  813K (0)|
|   2 |   INDEX FULL SCAN    | PK_DEPT |      1 |    1   (0)|      4 |00:00:00.01 |       1 |          |
|*  3 |   TABLE ACCESS FULL  | BIG_EMP |      1 |    2   (0)|  26000 |00:00:00.01 |     175 |          |
--------------------------------------------------------------------------------------------------------


위의 통계정보를 보면 176 블럭을  scan 했으며  Hash area size 를 813 K를 사용했다는걸 알수 있다.
작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택하고 BIG_EMP 테이블을
후행(Probe) 테이블로 Hash 조인 함으로서 최적의 조인이 되었다.
그렇다면 Hash Join Right Semi 를 사용하지 않으면 어떻게 될것인가?
Subquery Unnesting 기능을 이용하면 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택할수는 있다.
하지만 아래처럼 약간의 손해를 감수해야 한다.

select /*+ gather_plan_statistics ordered */ a.empno, a.sal
 from   big_emp a
 where  exists (select /*+ use_hash(b) */
                                b.deptno
                         from  dept b
                      where  b.deptno = a.deptno
                      );

위의 SQL 을 보면 강제로 MAIN 쿼리에 ordered 힌트를 주어 Semi Join 이 아닌 SubQuery Unnesting 이 되었다.
ordered 힌트를 사용한 이유는 서브쿼리가 Semi Join 에 실패할 경우  Subquery Unnesting 을 시도하게 되는데
이때 서브쿼리블럭이 From 절의 가장 좌측으로 오기 때문이다.
사용자가 ordered 힌트등을 사용하면 오라클 내부적인 leading 힌트와 Swap_join_inputs 힌트 등이 Override 되어 무시된다.
따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다.
이제 Plan 을 보자.

------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts |Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |         |      1 |    5  (40)|  26000 |00:00:00.37 |     176 |  808K (0)|
|   2 |   SORT UNIQUE      |         |      1 |    1   (0)|      4 |00:00:00.01 |       1 | 2048  (0)|
|   3 |    INDEX FULL SCAN | PK_DEPT |      1 |    1   (0)|      4 |00:00:00.01 |       1 |          |
|*  4 |   TABLE ACCESS FULL| BIG_EMP |      1 |    2   (0)|  26000 |00:00:00.31 |     175 |          |
------------------------------------------------------------------------------------------------------
 
처음 예제와 조인순서와 Scan 한 블럭수및 Hash area size 사용량은 대동소이 하지만 Subquery Unnesting 이 발생하여 불필요한 Sort 가 발생 하였다.
위의 SQL 의 경우 Subquery Unnesting 은 메인쿼리의 결과집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생된다.
Subquery Unnesting 이 항상 나쁜것은 아니지만 대용량 집합간의 조인시는 엄청난 부담이 될수 밖에 없다.
서브쿼리쪽은 Sort Unique 작업이 추가적으로 필요하기 때문이다.
그렇다고 덩치가 큰 BIG_EMP를 선행테이블로 할수도 없는것이다.
이것이 바로 Hash Join Right Semi 가 10g 에서 나타난 이유이다.
그렇다면 이럴 경우에 강제로 Hash Join Right Semi 를 발생시키려면 어떻게 해야 되겠는가?
이럴때 간단하게 사용할수 있는것이 QB_NAME 을 이용한 Global 힌트와 USE_HASH 및 SWAP_JOIN_INPUT 힌트이다.
아래의 스크립트를 보자.

select /*+ gather_plan_statistics LEADING(A) USE_HASH(@sub B) SWAP_JOIN_INPUTS(@sub B) */ a.empno, a.sal
 from   big_emp a
 where  exists (select /*+ qb_name(sub) */
                                b.deptno
                        from  dept b
                      where  b.deptno = a.deptno
                      );

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |     4  (25)|  26000 |00:00:01.05 |     176 |  813K (0)|
|   2 |   INDEX FULL SCAN    | PK_DEPT |      1 |     1   (0)|      4 |00:00:00.01 |       1 |          |
|*  3 |   TABLE ACCESS FULL  | BIG_EMP |      1 |     2   (0)|  26000 |00:00:00.99 |     175 |          |
---------------------------------------------------------------------------------------------------------

다시 정상적인 Hash Join Right Semi 로 돌아왔다.
간단히 힌트를 설명하자면 QB_NAME 은 쿼리블럭명을 강제로 지정하는 힌트이고 Swap_join_inputs 힌트는
Probe 쪽 집합(후행 집합) 을 강제로 Build Input 집합(선행집합) 으로 바꾸는 힌트이다.
그리고 Use_hash 힌트에 대하여 한마디 하자면 원래 Use_hash 힌트는 후행 집합에 대해서만 사용하는 힌트이다.
하지만 USE_HASH(A B) 이런식으로 사용해도 ORACLE 이 힌트를 아래처럼 변환시켜버린다.
USE_HASH(A B) --> LEADING(A B) USE_HASH(B)
오라클사에서 명시적인 용어가 없기 때문에 필자는 이것을  Internal Hint Transformation 이라 부른다.
다음에 기회가 되면 Internal Hint Transformation 에 대하여 글을 올릴까 한다.

결론 : 10g 부터 나온 Hash Join Right (Semi/Anti/Outer) 기능을 적재적소에 활용하면 대용량 집합간의 join 성능을 획기적으로 향상시킬수 있다.

참고로 Hash Join Right Anti Plan 으로 유도하는 것은 Exists 대신 Not Exists 로 바꾸면 된다.
Hash Join Right Outer 를 유도하는 예제는 아래와 같다.

select /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */ a.empno, a.sal
 from   big_emp a,
          dept b
where a.deptno = b.deptno(+)  
저작자표시 비영리 동일조건

'Oracle > Data Join Method' 카테고리의 다른 글

Oracle 조인 방법 25가지  (15) 2010.02.26
Nested Loop Join 성능향상과 관련된 2가지 원리  (17) 2009.05.09
Full Outer Join 의 비밀  (5) 2009.02.23
Hash 조인시 Bushy tree Plan 유도하기  (4) 2008.11.01
Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝)  (3) 2008.10.30
Posted by extremedb
,

Full Outer Join 의 비밀

Oracle/Data Join Method 2009. 2. 23. 21:00
한 개발자가 Full Outer Join 에 대한 실행계획에 대하여 질문을 해왔다.
그개발자는 아래와 같은 이야기를 설명 하였다.

하나의 테이블 기준으로 outer Join 을 한다
Union all
반대편 테이블을 기준으로 다시 outer join 을 한다.

여러분들은 위의 이야기를 어떻게 생각하는가?
개발자의 이야기는 반(윗부분)은 맞고 반(아랫부분)은 틀리다.

환경은 Oracle 10g 버젼 10.2.0.4 이다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ a.empno, a.ename, b.dname
  from  emp a full outer join dept b
    on  (a.deptno= b.deptno) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------

윗부분은 분명히 Outer 조인으로 풀렸지만 아래부분은 ANTI 조인으로 풀렸으므로 이것은 서브쿼리를 사용한것이다.
즉 옵티마이져는 아래처럼 Query Transformation 을 하게 되는 것이다.
 
select /*+ gather_plan_statistics */ *
from (
       select a.empno, a.ename, b.dname
       from  emp a, dept b
       where a.deptno = b.deptno(+)
       union all
       select null, null, a.dname
       from dept a
       where not exists (select 1
                                    from emp b
                                where  b.deptno = a.deptno
                                )
       ) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------
실행계획이 Full Outer Join 과 똑같음을 알수 있다.
하지만 이것도 어디까지나 파라미터 _optimizer_native_full_outer_join 가 off 일때 까지의 이야기이다.

아래의 스크립트를 보자.

/*+ opt_param('_optimizer_native_full_outer_join', 'force') */

위의 힌트를 사용하거나 아니면 아래처럼 ALTER SESSION 을 하면 된다.

alter session set "_optimizer_native_full_outer_join" = 'force';

select /*+ gather_plan_statistics */
      a.empno, a.ename, b.dname
from  emp a full outer join dept b
  on  (a.deptno= b.deptno) ;

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   1 |  VIEW                 | VW_FOJ_0 |      1 |     14 |00:00:00.01 |      15 |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |     14 |00:00:00.01 |      15 |  573K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT     |      1 |      4 |00:00:00.01 |       7 |          |
|   4 |    TABLE ACCESS FULL  | EMP      |      1 |     14 |00:00:00.01 |       8 |          |
----------------------------------------------------------------------------------------------

_optimizer_native_full_outer_join 파라미터를 force 로 하자 ANTI 조인마져 사라졌다.
이기능은 Native hash full outer join 이라고 불리고 11g 부터는 기본이 force 로 되어있다.

결론: Full Outer Join 은 10g 버젼 까지는 Outer Join + Union all + Anti Jojn 이지만
      11g 부터는 성능이 향상된 Native hash full outer join 를 사용할수 있음을 기억하자.
저작자표시 비영리 동일조건

'Oracle > Data Join Method' 카테고리의 다른 글

Nested Loop Join 성능향상과 관련된 2가지 원리  (17) 2009.05.09
Hash Join Right (Semi/Anti/Outer) 의 용도  (17) 2009.03.02
Hash 조인시 Bushy tree Plan 유도하기  (4) 2008.11.01
Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝)  (3) 2008.10.30
Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)  (6) 2008.10.12
Posted by extremedb
,

Outer Join 의 재조명

Oracle/Data Join Method 2008. 4. 7. 11:33

Outer Join 의 재조명

Outer Join 의 Lateral Views 로의 변환

목적 : 많은 사람들이 ANSI OUTER JOIN 에 대해 이해하지 못하고 심지어 튜너들이라고 하는사람들까지도 개념정립을 못하고 있다.

이 문서를 보고 개념정립을 확실히 하기바란다.

개념:
Oracle 에서는, ANSI left outer join은 내부적으로 left outer joined lateral views로 표현된다.
'lateral view'는 FROM절에서 앞서 나타난 다른 테이블들을 참조하는 관계를 포함한
inline view이다.
다시말하면 'lateral view'는 Scalar inline view 라고 보면된다.
'lateral view'는 스칼라 서브쿼리처럼 결과집합의 건수에 영향을 미치지 못한다.
Oracle 은 ANSI SQL 2003 의 'lateral view'를 지원하지 못하고 내부적으로만 사용하고 있다.

버그인가 아니면 정확한 결과인가?
아래 예제를 보고 헷갈릴수밖에 없는 이유를 설명한다.
데이터는 다음과 같다.
1) EMP 테이블
SELECT EMPNO, ENAME, DEPTNO
FROM EMP;
2) DEPT 테이블
SELECT DEPTNO, DNAME
FROM DEPT;


3) ANSI 문법으로 outer join 쿼리를 날려본다.
SELECT empno, ename, dname, d.deptno
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.deptno = D.deptno
AND E.empno > 7600;



뭔가 이상하다 사번이 7600 보다 큰건들만 나오지 않고 전체가 나와 버린다.
버그인가?
전혀아니다.
오라클 옵티마이져는 위 SQL 이 나오면 아래와 같이 변환한다.
변환된 모습을 보면 결과를 예측할수 있다.
'lateral view' 개념을 적용


위의 쿼리변형결과를 보고 아래와 같은 결론을 내릴수있다.
1)위의 'lateral view' 는 결과 건수에 영향을 미치지 못하는 스칼라 인라인뷰이다.(물론 건수가 늘어나는 경우도 있음)
2)E.empno > 7600 조건은 결과건수에 영향을 못미치고 DEPT 와의 조인건수에만
영향을 끼친다.
다시말하면 E.empno > 7600 에 만족하는건만 DEPT 와 조인한다.
--> 위개념을 이용한 선택적인 조인으로 튜닝하는 방법을 추후에 작성할 예정임.
3)ANSI OUTER JOIN 이 헷갈릴 경우는 Lateral View 개념을 적용하여 쿼리를 작성하면 된다.

그렇다면 아래와 같은 쿼리는 결과가 어떻게 나올것인가

SELECT empno, ename, dname, d.deptno
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.deptno = D.deptno
WHERE E.empno > 7600;
위쿼리를 'lateral view' 개념을 적용하면 아래와 같다



위쿼리를 보면 메인쿼리의 WHERE 절이 추가된것을 알수 있다.
결과도 당연히 사번이 7600 보다 큰건만 나오게 된다.


'Oracle > Data Join Method' 카테고리의 다른 글

Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝)  (3) 2008.10.30
Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)  (6) 2008.10.12
New Features 의 중요성(Partition Outer Join 의 사용법)  (5) 2008.08.29
Parallel Query 의 조인시 Row Distribution  (3) 2008.07.19
Lateral View 를 활용한 선택적 조인  (2) 2008.05.29
Posted by extremedb
,

티스토리툴바