레이블이 오라클팁인 게시물을 표시합니다. 모든 게시물 표시
레이블이 오라클팁인 게시물을 표시합니다. 모든 게시물 표시

2013년 8월 3일 토요일

[Oracle SQL Explain Plan]ORACLE HINT 강좌 실행계획 SQL 연산(HASH ANTI-JOIN)

실행계획 SQL 연산(HASH ANTI-JOIN

구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr

ANTI 조인은 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산 입니다. SQL연산에서 NOT IN, NOT EXISTS, MINUS등이 해당되며 이러한 안티 조인은 MERGE ANTI-JOIN or HASH ANTI_JOIN으로 풀리도록 할 수 있습니다.

아래의 Query는 동일한 의미를 가지는 질의 입니다. 확인해 보세요~

create table myemp1
(empno number not null primary key,
 ename varchar2(100),
 deptno number,
 addr   varchar2(100),
 sal    number
 )

-- 실습을 위해 myemp1을 1000만건 만들자.
DECLARE
          v_c NUMBER := 1;
BEGIN

          WHILE (v_c <= 10000000) LOOP
                insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
          END LOOP;
          commit;
END;


create table myemp1_old
as select * from myemp1 where rownum < 1000000


  

 -- 통계정보 셍성
    exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'MYDEPT1_OLD')
   
    exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'MYDEPT1')




별다른 인덱스가 없는 상태에서 실행해 보자. 37초 걸린다.
오라클 11g에서 기본적으로 merge anti joinm으로 실행계획을 잡는다.


SQL> select count(e1.ename) 
  2   from myemp1 e1
  3   where (ename, sal) not in (select ename, sal
  4                                from myemp1_old e2);

COUNT(E1.ENAME)--28
-------------------
            9000001

경   과: 00:00:37.87

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |    82 |       | 90492  
|   1 |  SORT AGGREGATE      |            |     1 |    82 |       |           
|   2 |   MERGE JOIN ANTI NA |            |    10M|   782M|       | 90492  
|   3 |    SORT JOIN         |            |    10M|   162M|   536M| 72263  
|   4 |     TABLE ACCESS FULL| MYEMP1     |    10M|   162M|       | 16961  
|*  5 |    SORT UNIQUE       |            |  1070K|    66M|   156M| 18229  
|   6 |     TABLE ACCESS FULL| MYEMP1_OLD |  1070K|    66M|       | 




이번에는 HASH ANTI JOIN으로 힌트를 주고 실행하자. ANTI JOIN 일때는 where절의 not in 출현 컬럼에 대해 is not null 조건을 주도록 하자.

SQL> select
           count(e1.ename)
  from myemp1 e1
  where (ename, sal) not in (select
                                    ename, sal
                               from myemp1_old e2
                               where ename is not null
                                 and sal is not null)
 and ename is not null
  and    sal is not null
 /

실행해 보면 수행시간이 절반 이상으로 준다. MERGE ANTI JOIN 보다 HASH ANTI JOIN 성능이 낫다.

COUNT(E1.ENAME)
---------------
        9000001

경   과: 00:00:14.92

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    82 |       | 36297  
|   1 |  SORT AGGREGATE       |            |     1 |    82 |       |
|*  2 |   HASH JOIN RIGHT ANTI|            |    10M|   782M|    78M| 36297  
|*  3 |    TABLE ACCESS FULL  | MYEMP1_OLD |  1070K|    66M|       | 
|*  4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|   162M|       |



SQL> SELECT count(ename)
  2                FROM   MYEMP1 E
  3  WHERE  NOT EXISTS (SELECT  1
  4                     FROM MYEMP1_OLD EO
  5                     WHERE  EO.ENAME = E.ENAME
  6                     AND     EO.SAL    = E.SAL)
  7  and ename is not null
  8  and   sal is not null;

COUNT(ENAME)
------------
     9000001

경   과: 00:00:11.56

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    82 |       | 36295  
|   1 |  SORT AGGREGATE       |            |     1 |    82 |       |
|*  2 |   HASH JOIN RIGHT ANTI|            |    10M|   782M|    78M| 36295  
|   3 |    TABLE ACCESS FULL  | MYEMP1_OLD |  1070K|    66M|       | 
|*  4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|   162M|       |



이번에는 HASH_AJ 힌트를 사용해 보자.
수행 시간은 대략 비슷하다.
SQL> SELECT count(ename)
  2                FROM   MYEMP1 E
  3  WHERE  NOT EXISTS (SELECT  /*+ hash_aj */1
  4                     FROM MYEMP1_OLD EO
  5                     WHERE  EO.ENAME = E.ENAME
  6                     AND     EO.SAL    = E.SAL)
  7  and ename is not null
  8  and   sal is not null;

COUNT(ENAME)
------------
     9000001

경   과: 00:00:11.00

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    82 |       | 36295  
|   1 |  SORT AGGREGATE       |            |     1 |    82 |       |
|*  2 |   HASH JOIN RIGHT ANTI|            |    10M|   782M|    78M| 36295  
|   3 |    TABLE ACCESS FULL  | MYEMP1_OLD |  1070K|    66M|       | 
|*  4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|   162M|       |



이번에는 MINUS로 풀어 보자.

SQL> with a as (
  2      select  ename, sal
  3      from myemp1
  4      minus
  5      select  ename, sal
  6      from myemp1_old
  7   )
  8  select count(ename) from a  ;

COUNT(ENAME)
------------
     9000001

경   과: 00:00:30.48

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    52 |       | 90492  
|   1 |  SORT AGGREGATE       |            |     1 |    52 |       |
|   2 |   VIEW                |            |    10M|   495M|       | 90492  
|   3 |    MINUS              |            |       |       |       |
|   4 |     SORT UNIQUE       |            |    10M|   162M|   268M| 72263  
|   5 |      TABLE ACCESS FULL| MYEMP1     |    10M|   162M|       |
|   6 |     SORT UNIQUE       |            |  1070K|    66M|    78M| 18229  
|   7 |      TABLE ACCESS FULL| MYEMP1_OLD |  1070K|    66M|       | 


HASH ANTI JOIN으로 풀 수 있는 것은 NOT IN을 포함하고 있는 첫 번째 질의에서 가장 좋은 성능을 보이며 NOT IN의 비교 대상이 되는 컬럼은 NOT NULL로 서브쿼리까지 명시해 주어야 합니다. 물론 HASH_AJ 라는 힌트 구문도 사용해야 하구요~

HSH ANTI JOIN으로 풀 경우 성능이 향상되므로 위 문장과 같이 한 테이블에 존재하지 않는 로우만 추출하는 경우엔 HASH ANTI JOIN이  되도록 힌트를 사용하는 것이 유리합니다.


이번에는 조금 더 개선을 해서 EMPTEST TABLE의  EMPNO, ENAME, SAL 컬럼으로 비트맵 인덱스를 구성해서 쿼리를 해 보자.

create bitmap index idx_bitmap_empno_ename_sal on emptiest (empno, ename, sal)

select   /*+ index(idx_bitmap_empno_ename_sal e1) */
          count(e1.ename)  
 from emptest e1
 where (ename, sal) not in (select
                                   ename, sal
                              from emptest_old e2
                              where ename is not null
                                and sal is not null)
 and    ename is not null
 and    sal is not null


작성자: 시간: 댓글 없음:
라벨: , , , , , , ,

2013년 8월 2일 금요일

[SQL초보전문가]조인 방법 변경(USE_MERGE) , SQL교육,오라클힌트 교육, SQL강좌

Hint]조인 방법 변경(USE_MERGE)
 
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
 
 
머지 조인(Merge Join)이 일어나도록 유도하는 힌트 구문으로 이 경우 거의 SORT를 동반하므로 SORT MERGE JOIN이라고 부릅니다. 머지 조인이란 양쪽 테이블에서 대상 로우를 추출 후 조인 컬럼을 기준으로 SORT를 한 후 최종 결과를 만들어 내는 조인 방식 입니다.
 
USE_NL처럼 FROM 절 다음에 위치하는 테이블의 순서는 중요하지 않은데 그 이유는 어차피 독립적으로 정렬된 후 병합이 일어나므로 중요하지 않다고 할 수 있으며 SORT MERGE JOIN에서는 드라이빙 테이블의 의미가 없습니다.
 
[형식]
 
 
[예]
아래 예제는 Oracle 10g에서 돌렸습니다.
 
 
select 
       e.empno,
          e.ename,
          d.dname,
          d.loc
from   dept d, emp e
where  e.deptno = d.deptno
 
---------------------------------------------------------------
Operation            Object Name      Rows     Bytes    Cost     
-------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS               14                      5
  MERGE JOIN                  14         406       5                                                       
    TABLE ACCESS BY INDEX ROWID         SCOTT.DEPT      4           72         2            
      INDEX FULL SCAN   SCOTT.PK_DEPT             4                        1            
    SORT JOIN                 14         154       3                                                       
      TABLE ACCESS BY INDEX ROWID      SCOTT.EMP        14         154       2            
        INDEX FULL SCAN             SCOTT.IDX_EMP_DEPTNO            13                      1                                                           
 
select 
       e.empno,
          e.ename,
          d.dname,
          d.loc
from   dept d, emp e
where  e.deptno = d.deptno
 
---------------------------------------------------------------------
Operation            Object Name      Rows     Bytes    Cost     
------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS               14                      5
  MERGE JOIN                  14         406       5                                                       
    TABLE ACCESS BY INDEX ROWID         SCOTT.DEPT      4           72         2            
      INDEX FULL SCAN   SCOTT.PK_DEPT             4                        1                SORT JOIN                14         154               3                                                       
      TABLE ACCESS BY INDEX ROWID      SCOTT.EMP        14         154       2            
        INDEX FULL SCAN             SCOTT.IDX_EMP_DEPTNO            13                      1                                                           
 
[실습]
 
-      실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&page=0&sca=&sfl=&stx=&sst=&sod=&spt=0&page=0
 
myemp1 : 1000만건
myemp1_old : 100만건
mydept : 5건
 
테스트환경 : oracle 11g
 
 
MYEMP1이 비드라이빙 테이블이지만 머지조인 에서는 별 의미 없다.
 
SQL> select
  2         e.ename,
  3         d.dname
  4  from   mydept1 d, myemp1 e
  5  where  e.deptno = d.deptno   ;
 
20000000 개의 행이 선택되었습니다.
 
경   과: 00:02:22.62
 
 
---------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    20M|   476M|       | 68516   (2)| 00:13:43 |
|   1 |  MERGE JOIN         |         |    20M|   476M|       | 68516   (2)| 00:13:43 |
|   2 |   SORT JOIN         |         |    10 |   100 |       |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| MYDEPT1 |    10 |   100 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |         |    10M|   143M|   459M| 68463   (1)| 00:13:42 |
|   5 |    TABLE ACCESS FULL| MYEMP1  |    10M|   143M|       | 16941   (1)| 00:03:24 |
 
 
SQL> select
  2         e.ename,
  3         d.dname
  4  from   myemp1 e, mydept1 d
  5  where  e.deptno = d.deptno ;
 
20000000 개의 행이 선택되었습니다.
 
경   과: 00:02:09.58
 
---------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    20M|   476M|       | 68516   (2)| 00:13:43 |
|   1 |  MERGE JOIN         |         |    20M|   476M|       | 68516   (2)| 00:13:43 |
|   2 |   SORT JOIN         |         |    10M|   143M|   459M| 68463   (1)| 00:13:42 |
|   3 |    TABLE ACCESS FULL| MYEMP1  |    10M|   143M|       | 16941   (1)| 00:03:24 |
|*  4 |   SORT JOIN         |         |    10 |   100 |       |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| MYDEPT1 |    10 |   100 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

  
작성자: 시간: 댓글 없음:
라벨: , , , , , , , , , ,

[oracle hint]조인 방법 변경(USE_HASH) , 오라클힌트강좌,오라클실무전문교육,오엔제이프로그래밍

Hint]조인 방법 변경(USE_HASH)
 
구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr
 
해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식 입니다.
 
성능을 위해서는 당연히 사이즈가 작은 테이블이 메모리에 올라가는 것이 좋은데 이때 이 테이블을 드라이빙 테이블(driving/outer table) 이라고 합니다. 특히 이 해시 테이블이 메모리에 생성되면 성능은 좋으며(메모리에 생성되지 않으면 내부적으로 임시 테이블이 만들어 져야 합니다.) 두 테이블의 크기 차이가 클수록 성능은 좋아집니다.
 
또한 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합 합니다.
 
 
[형식]
 
select  … from 작은테이블, 큰테이블
 
 
[예]
아래는 Oracle 10g에서 테스트 했습니다.
 
아래에서 dept 테이블이 메모리에 로드되어 emp 테이블의 내용과 비교하면서 결과를 추출 합니다.
 
select    
           e.empno,
          e.ename,
          d.dname,
          d.loc
from   dept d, emp e
where  e.deptno = d.deptno
 
---------------------------------------------------------------
Operation            Object Name      Rows     Bytes    Cost     
-------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS               14                      6
  HASH JOIN                    14         406       6                                                       
    TABLE ACCESS FULL             SCOTT.DEPT      4           72         3                         
    TABLE ACCESS BY INDEX ROWID         SCOTT.EMP        14         154       2            
      INDEX FULL SCAN   SCOTT.IDX_EMP_DEPTNO            13                      1 
 
 
select 
       e.empno,
          e.ename,
          d.dname,
          d.loc
from   emp e, dept d
where  e.deptno = d.deptno
 
------------------------------------------------------------------
Operation            Object Name      Rows     Bytes    Cost     
---------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS               14                      6
  HASH JOIN                    14         406       6                                                       
    TABLE ACCESS BY INDEX ROWID         SCOTT.EMP        14         154       2            
      INDEX FULL SCAN   SCOTT.IDX_EMP_DEPTNO            13                      1
    TABLE ACCESS FULL             SCOTT.DEPT      4           72         3                              
 
참고로 ALL_ROWS인 경우엔 머지 조인과 해시 조인을 비교한다면 해시 조인의 성능이 좋으며 중첩 조인의 경우 주로 첫번째 로우를 빠르게 추출하기 위한 FIRST_ROWS로 수행되는 조인 입니다.
 
 
 
 
[실습]
 
-      실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.
http://www.onjprogramming.co.kr/oraclejavanew/oraclejava/bbs/board.php?bo_table=LecOrccleTun&wr_id=53&page=0&sca=&sfl=&stx=&sst=&sod=&spt=0&page=0
 
myemp1 : 1000만건
myemp1_old : 100만건
mydept : 5건
 
테스트환경 : oracle 11g
 
 
MYDEP1 테이블이 드라이빙 테이블
 
SQL> select
  2         e.ename,
  3         d.dname
  4  from   mydept1 d, myemp1 e
  5  where  e.deptno = d.deptno  ;
 
20000000 개의 행이 선택되었습니다.
 
경   과: 00:01:42.32
 
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    20M|  1525M| 17043   (2)| 00:03:25 |
|*  1 |  HASH JOIN         |         |    20M|  1525M| 17043   (2)| 00:03:25 |
|   2 |   TABLE ACCESS FULL| MYDEPT1 |    10 |   650 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| MYEMP1  |    10M|   143M| 16941   (1)| 00:03:24 |
------------------------------------------------------------------------------
 
 
 
 
이번에는 MYEMP1 테이블이 드라이빙 테이블이 된다.
 
SQL> select
  2         e.ename,
  3         d.dname
  4  from   myemp1 e, mydept1 d
  5  where  e.deptno = d.deptno ;
 
20000000 개의 행이 선택되었습니다.
 
경   과: 00:02:03.14
 
--------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    20M|  1525M|       | 29883   (1)| 00:05:59 |
|*  1 |  HASH JOIN         |         |    20M|  1525M|   257M| 29883   (1)| 00:05:59 |
|   2 |   TABLE ACCESS FULL| MYEMP1  |    10M|   143M|       | 16941   (1)| 00:03:24 |
|   3 |   TABLE ACCESS FULL| MYDEPT1 |    10 |   650 |       |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------- 
작성자: 시간: 댓글 없음:
라벨: , , , , , , , , ,
피드 구독하기: 글 (Atom)