Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용]

Transcription

1 Special Key Note Oracle Data Join Method ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1

2 What is Join? JOIN is Multiply. 2

3 Why is the Join Method so important? 잘못사용하면큰재앙이따른다.( 위의그림처럼 ) 두개의집합을연결할수있는유일한수단. Join Method 는모든 DBMS 가대동소이. 3

4 Learning Join Method - Any Benefit? Learning Join Before Learning Join After Execution Plan 이눈에들어옴. 비로소 SQL 튜닝이가능해짐. 적절한 Join Method 를적소에사용하는것이 SQL 튜닝임. Data Access Pettern 과밀접한관련이있음. 4

5 Oracle Data Join Method Nested Loop Join Sort Merge Join Hash Join Outer Join Using SubQuery Join Method 에따라용도가다르다. 5

6 Oracle Data Join Method Contents Nested Loop Join Sort Merge Join Hash Join Cartesian Join ( 혹은 Cross Join) Sub Query (In, Any, All, Exists, Subquery Factoring) Semi Join (Nested Loop, Sort Merge, Hash, Hash Join Right) Anti Join (Nested Loop, Sort Merge, Hash, Hash Join Right) Index Join Outer Join (Full,Nested Loop, Sort Merge, Hash, Hash Join Right) Partition Outer Join Star Query Transformation 6

7 Nested Loop Join > 특징 > 적용 > Hint 먼저수행되는집합 (Driving) 의처리범위가전체일량을좌우 먼저수행되는집합 (Driving) 이상수로바뀌어후행테이블에공급된다. 후행테이블은계속 Loop 를돌면서 Driving 테이블의상수공급이끝날때까지조인한다. Single Block I/O Random Access 발생 ( 테이블 access 가필요한경우 ) 소량의데이터처리 (OnLine) 는유리 대량의데이터처리 (Batch) 는큰부하발생 ( 페이징처리된 SQL 은예외 ) OLTP 시스템 처리해야하는범위가소량인경우 (10 만건미만 ) 부분범위 ( 페이징 ) 처리시 select /*+ use_nl(a b) */ a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno ; 7

8 Nested Loop Join -Concept > Full Unique 인덱스 layout : dept(deptno) pk select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno ; EMP 테이블 PK_DEPT 인덱스 DEPT 테이블 EMPNO ENAME DEPTNO. DEPTNO DNAME LOC 7782 CLARK SCOTT FORD KING ACCOUNTING NEW YORK 30 SALES CHICAGO 20 RESEARCH DALLAS 40 OPERATIONS BOSTON Full Table Scan Multi Block I/O 8

9 Nested Loop Join - 예제 > Full Unique 실행계획 인덱스 layout : dept(deptno) p pk select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno ; Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads NESTED LOOPS :00: TABLE ACCESS FULL EMP :00: TABLE ACCESS BY INDEX ROWID DEPT :00: * 4 INDEX UNIQUE SCAN PK_DEPT :00: Predicate Information (identified by operation id): access("a"."deptno"="b"."deptno") 9

10 Nested Loop Join -Concept >Full Range scan 인덱스 layout : emp(deptno) 생성 dept(deptno) pk select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno ; EMP 테이블 Full Table Scan Multi Block I/O EMPNO ENAME DEPTNO TURNER 30. DEPT 테이블 DEPTNO DNAME LOC 10 ACCOUNTING NEW YORK 30 SALES CHICAGO 20 RESEARCH DALLAS... EMP.DEPT 인덱스 CLARK SCOTT FORD KING

11 Nested Loop Join - 예제 > Full Range 실행계획 인덱스 layout : emp(deptno) 생성 dept(deptno) pk select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno ; Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: NESTED LOOPS :00: TABLE ACCESS FULL DEPT :00: * 4 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("a"."deptno"="b"."deptno") 11

12 Nested Loop Join -Concept >Range Range 인덱스 layout : dept(deptno) PK emp(deptno) select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno and b.deptno BETWEEN 10 AND 20; EMP 테이블 EMPNO ENAME DEPTNO. PK_DEPT DEPT 테이블 EMP.DEPT 7782 CLARK 10 인덱스 인덱스 DEPTNO DNAME LOC 7782 CLARK ACCOUNTING NEW YORK 30 SALES CHICAGO SCOTT RESEARCH DALLAS OPERATIONS BOSTON FORD KING

13 Nested Loop Join - 예제 > Range-Rane 실행계획 인덱스 layout : dept(deptno) PK emp(deptno) select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno and b.deptno BETWEEN 10 AND 20; Id Operation Name Starts E-Rows A-Rows A-Time Buffers TABLE ACCESS BY INDEX ROWID EMP :00: NESTED LOOPS :00: TABLE ACCESS BY INDEX ROWID DEPT :00: * 4 INDEX RANGE SCAN PK_DEPT :00: * 5 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("b"."deptno">=10 AND "B"."DEPTNO"<=20) 5 - access("a"."deptno"="b"."deptno") filter(("a"."deptno"<=20 AND "A"."DEPTNO">=10)) 13

14 Nested Loop Join -Concept > Unique Unique 인덱스 layout : dept(deptno) PK emp(empno) PK select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno and a.empno = 7839; EMPNO 인덱스 EMP 테이블 EMPNO ENAME DEPTNO. PK_DEPT 인덱스 DEPT 테이블 인덱스 DEPTNO DNAME LOC 7839 KING ACCOUNTING NEW YORK 30 SALES CHICAGO 20 RESEARCH DALLAS 7782 CLARK OPERATIONS BOSTON 7788 SCOTT FORD

15 Nested Loop Join - 예제 > Unique-Unique 실행계획 인덱스 layout : dept(deptno) PK emp(empno) PK select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno and a.empno = 7839; Id Operation Name Starts E-Rows A-Rows A-Time Buffers NESTED LOOPS :00: TABLE ACCESS BY INDEX ROWID EMP :00: * 3 INDEX UNIQUE SCAN PK_EMP :00: TABLE ACCESS BY INDEX ROWID DEPT :00: * 5 INDEX UNIQUE SCAN PK_DEPT :00: Predicate Information (identified by operation id): access("a"."empno"=7839) 5 - access("a"."deptno"="b"."deptno") 15

16 Sort Merge Join > 특징 조인되는컬럼에인덱스존재유무가문제되지않음 정렬 (Sort) 을대신할인덱스가존재할경우부하감소 > 적용 > Hint 처리량이많은전체범위에주로사용 (Batch) 부분범위처리 ( 페이징처리 ) 가안되며 Nested Loop Join 의 Random 액세스가크게부담이되는경우양쪽테이블을 full table scan 을사용함으로서부하를경감시킬수있음. 조인된컬럼을기준으로결과집합을 sort 해야될경우최적의적용조건임. /*+ use_merge(a b) */ select /*+ use_merge(a b) */ a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno Order by b.deptno ; 16

17 Sort Merge Join -Concept >Range Merge Range select /*+ use_merge(a b) */ a.dname, b.empno, b.ename from dept a, big_emp b where a.deptno = b.deptno and a.deptno between 10 and 20 and b.hiredate between to_ date(' ', 'yyyymmdd') and to_date(' ', 'yyyymmdd') order by b.deptno ; PK_DEPT DEPT BIG_EMP IX_BIG_EMP_N3 S O R T Merge S O R T 결과집합 RETURN 17

18 Sort Merge Join 예제1 > 실행계획 select /*+ use_merge(a b) */ a.dname, b.empno, b.ename from dept a, big_emp b where a.deptno = b.deptno and a.deptno between 10 and 20 and b.hiredate between to_date(' ', 'yyyymmdd') and to_date(' ', 'yyyymmdd') order by b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem MERGE JOIN :00: SORT JOIN :00: (0) 3 TABLE ACCESS BY INDEX ROWID DEPT :00: * 4 INDEX RANGE SCAN PK_DEPT :00: * 5 SORT JOIN :00: K (0) 6 TABLE ACCESS BY INDEX ROWID BIG_EMP :00: * 7 INDEX RANGE SCAN IX_BIG_EMP_N :00: Predicate Information (identified by operation id): access("a"."deptno">=10 AND "A"."DEPTNO"<=20) 5 - access("b"."deptno"=to_number("a"."deptno")) filter("b"."deptno"=to_number("a"."deptno")) 7 - access("b"."hiredate">=to_date(' :00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."HIREDATE"<=TO_DATE(' :00:00', 'syyyy-mm-dd hh24:mi:ss')) 18

19 Sort Merge Join -Concept >Full Merge Full select /*+ use_merge(a b) */ a.dname, b.empno, b.ename from dept a, big_emp b where a.deptno = b.deptno and b.sal > 1000 ; DEPT BIG_EMP S O R T Merge S O R T 결과집합 RETURN 19

20 Sort Merge Join 예제2 > 실행계획 select /*+ use_merge(a b) */ a.dname, b.empno, b.ename from dept a, big g_ emp pb where a.deptno = b.deptno and b.sal > 1000 ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem MERGE JOIN :00: SORT JOIN :00: (0) 3 TABLE ACCESS FULL DEPT :00: * 4 SORT JOIN :00: K (0) * 5 TABLE ACCESS FULL BIG_EMP :00: Predicate Information (identified by operation id): access("b"."deptno"=to_number("a"."deptno")) filter("b"."deptno"=to TO_NUMBER( NUMBER("A"."DEPTNO")) 5 - filter("b"."sal">1000) 20

21 Hash Join > 특징 > 적용 > Hint 먼저처리되는집합 (Driving) 이소량일때성능극대화됨. 연산자의제약 : 조인조건이 = (Equal) 조건에서만가능 메모리내에서수행시빠른속도보장 메모리사용량 (HASH_AREA_SIZE AREA SIZE ) 과 CPU 사용량이많음. Nested Loop 에서의 Random 액세스가부담스러울때 Sort Merge Join 에서의정렬 (Sort) 작업이부담스러울때 초대용량테이블을조인해야되는경우 ( 몇억건이상 ) 대량의데이터처리, Batch 처리, 테이블 FULL 스캔시유리 Parallel Query 와함께사용시수행속도극대화 온라인프로그램에함부로적용하지말것. /*+ use_hash(a b) */ SELECT /*+ use_hash(a b) */ a.empno, a.ename, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno ; 21

22 Hash Join - Concept >Range Hash Range 용어정리 Build Input 테이블 : Driving 테이블을의미함 Probe 테이블 : 후행테이블을의미함 Hash Table : Build Input 테이블을담아두는임시적인공간을의미함이공간은대부분메모리영역이며부족할경우 Disk 를사용하게됨으로성능이저하됨. select /*+ use_hash(a b) */ a.dname, b.empno, b.ename from dept a, big_emp b where e a.deptno = b.deptno and a.deptno between 10 and 20 and b.hiredate between to_date(' ', 'yyyymmdd') and to_date(' ', 'yyyymmdd') ; PK_DEPT DEPT BIG_EMP IX_BIG_EMP_N3 HASH TABLE BUILD INPUT TABLE PROBE TABLE 22

23 Hash Join 예제1 > 실행계획 select /*+ use_hash(a b) */ a.dname, b.empno, b.ename from dept a, big_emp b where a.deptno = b.deptno and a.deptno between 10 and 20 and b.hiredate between to_date(' ', 'yyyymmdd') and to_date(' ', 'yyyymmdd') ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 HASH JOIN :00: K (0) 2 TABLE ACCESS BY INDEX ROWID DEPT :00: * 3 INDEX RANGE SCAN PK_DEPT :00: TABLE ACCESS BY INDEX ROWID BIG_EMP :00: * 5 INDEX RANGE SCAN IX_BIG_EMP_N :00: Predicate Information (identified by operation id): access("b"."deptno"=to_number("a"."deptno")) 3 - access("a"."deptno">='10' AND "A"."DEPTNO"<='20') 5 - access("b"."hiredate">=to_date(' :00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."HIREDATE"<=TO_DATE(' :00:00', 'syyyy-mm-dd hh24:mi:ss')) 23

24 Hash Join - Concept >Full Hash Full(Probe) SELECT /*+ use_hash(a b) */ a.empno, a.ename, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno ; DEPT HASH TABLE EMP BUILD INPUT TABLE PROBE TABLE 24

25 Hash Join 예제2 > 실행계획 SELECT /*+ use_hash(a b) */ a.empno, a.ename, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 HASH JOIN :00: K (0) 2 TABLE ACCESS FULL DEPT :00: TABLE ACCESS FULL EMP :00: Predicate Information (identified by operation id): access("a"."deptno"=to_number("b"."deptno")) "DEPTNO" TO NUMBER("B" "DEPTNO")) 25

26 Cartesian Join > 발생조건 > 특징 > 적용 > Hint 조인되는두집합에조인조건이전혀없는경우 M:M 조인을의미 Cartesian Join 발생원인 - SQL 작성자의실수 - 사용자가특별한목적하에고의적발생 ( 데이터복제 ) Cross Join 이라고도함. 실행계획의특징 - Sort Merge 조인만이 Cartesian 실행계획명기 - 타조인의경우정상적인조인의실행계획으로명기, 단결과로확인가능 사용자가특별한목적하에조인조건없이사용 Cartesian Join 이발생하면 SQL 의조인조건을검증해야함. N/A 26

27 Cartesian Join -예제 > 실행계획 select a.empno, a.ename, b.dname from emp a, dept b order by b.dname ; 조인조건이빠져있음 Id Operation Name Starts E-Rows A-Rows A-Time Buffers OMem 1Mem Used-Mem SORT ORDER BY :00: (0) 2 MERGE JOIN CARTESIAN :00: TABLE ACCESS FULL DEPT :00: BUFFER SORT :00: (0) 5 TABLE ACCESS FULL EMP :00: Id Operation Name Starts E-Rows A-Rows A-Time Buffers OMem 1Mem Used-Mem SORT ORDER BY :00: (0) 2 NESTED LOOPS :00: TABLE ACCESS FULL DEPT :00: TABLE ACCESS FULL EMP :00: 실행계획상에서 Merge Join 일때만 Cartesian 이라는실행계획이보임 27

28 Sub Query > 발생조건 > 특징 > 적용 SELECT 한결과를조건비교 (>, =, <, IN, ANY, ALL, Exists) 에사용하거나 UPDATE, INSERT, DELETE에사용할때사용되는 Query를이르는말 서브쿼리종류에는 row 기준으로비교할경우 Single Row, Multi Row 가있음 서브쿼리종류에는 column 기준으로비교할경우 Single column, Multi column 가있음 Non Corelate 서브쿼리 : ( 서브쿼리내에서브쿼리와메인쿼리의조인절이없음 ) Corelate 서브쿼리 : ( 서브쿼리내에서브쿼리와메인쿼리의조인절이있음 ) 서브쿼리가조건의비교문으로사용될때연산자가 = 인경우서브쿼리에서나오는결과행의수가 1보다클수없고, Single Row 서브쿼리라함 연산자가 IN, ANY, ALL, EXISTS 등이사용되면 Multi Row 서브쿼리가능 Single Row 서브쿼리는특정한하나의값을메인쿼리에제공하고자할때적용 in 대신에 = 기호를사용해도됨 Multi Column 서브쿼리는보통 Primary Key 컬럼이두개이상인경우에 KEY 값을한꺼번에묶어서비교하기위해자주사용 제공자로사용할건지확인자로사용할건지확인하는습관을들여야함. 28

29 Sub Query(In) - 예제 > 실행계획 select deptno, empno, ename, job from emp where deptno in (select deptno from dept where loc in ('CHICAGO','DALLAS' )) ; Id Operation Name Starts E-Rows A-Rows A-Time Buffers TABLE ACCESS BY INDEX ROWID EMP :00: NESTED LOOPS :00: INLIST ITERATOR :00: TABLE ACCESS BY INDEX ROWID DEPT :00: * 5 INDEX RANGE SCAN DEPT_IDX :00: * 6 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access(("loc"='chicago' CHICAGO OR "LOC"='DALLAS')) 6 - access("deptno"="deptno") 29

30 Sub Query(=) - 예제 > 실행계획 sselect deptno, empno, ename, job from emp where deptno = (select deptno from dept where loc in ('CHICAGO' )) ; Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: * 2 INDEX RANGE SCAN IX_EMP_N :00: TABLE ACCESS BY INDEX ROWID DEPT :00: * 4 INDEX RANGE SCAN DEPT_IDX :00: Predicate Information (identified by operation id): access("deptno"=) 4 - access("loc"='chicago') 30

31 Sub Query(Any) - 예제 > 실행계획 서브쿼리의결과값중하나의값만만족하여도결과값을리턴 select empno, ename, job, sal from emp where sal > any (select sal from emp where job = 'MANAGER ) ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem MERGE JOIN SEMI :00: SORT JOIN :00: (0) 3 TABLE ACCESS BY INDEX ROWID EMP :00: INDEX FULL SCAN IX_EMP_N :00: * 5 SORT UNIQUE :00: (0) 6 TABLE ACCESS BY INDEX ROWID EMP :00: * 7 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access(internal_function("sal")>internal_function("sal")) filter(internal_function("sal")>internal_function("sal")) ( ( )) 7 - access("job"='manager') 31

32 Sub Query(All) - 예제 > 실행계획 서브쿼리의결과값을모두만족되어야결과값을리턴 select empno, ename, job, sal from emp where sal > all (select sal from emp where job = 'MANAGER ) ; Id Operation Name Starts A-Rows A-Time Buffers * 1 FILTER :00: TABLE ACCESS FULL EMP :00: * 3 TABLE ACCESS BY INDEX ROWID EMP :00: * 4 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): filter( IS NULL) 3 - filter(lnnvl("sal"<:b1)) 4 - access("job"='manager') 32

33 Sub Query(Exists) - 예제 > 실행계획 서브쿼리의데이터가존재하는가의여부를따져존재하는값들만결과로리턴 select b.deptno, b.dname from dept b where exists (select 1 from emp a where a.deptno = b.deptno) order by b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem SORT ORDER BY :00: (0) 2 NESTED LOOPS SEMI :00: TABLE ACCESS FULL DEPT :00: * 4 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("a"."deptno"=to =TO_NUMBER( NUMBER("B" B."DEPTNO")) 33

34 Sub Query(Sub Query Factoring) - 예제 > 실행계획 with 절을사용하여생성한복잡한쿼리문을임시테이블이실제로저장해두었다가거의테이블과동일하게사용할수있는기능 with x as (select /*+ materialize */ * from emp), y as (select /*+ materialize */ * from dept) select x.empno, x.ename, y.dname from x, y where x.deptno = y.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem TEMP TABLE TRANSFORMATION :00: LOAD AS SELECT :00: K (0) 3 TABLE ACCESS FULL EMP :00: LOAD AS SELECT :00: K (0) 5 TABLE ACCESS FULL DEPT :00: * 6 HASH JOIN :00: K (0) 7 VIEW :00: TABLE ACCESS FULL SYS_TEMP_0FD9FC965_61A :00: VIEW :00: TABLE ACCESS FULL SYS_TEMP_0FD9FC964_61A :00: Predicate Information (identified by operation id): access("x"."deptno"=to_number("y"."deptno")) 34

35 Semi/Anti Join > 발생조건 > 특징 > 적용 > Hint Sub Query IN, EXISTS 사용시 SEMI 조인발생 NOT IN, NOT EXISTS 사용시 ANTI 조인발생. Sub Query 를 Join 으로바꾸는방식임 일반적인 Join 과매우유사하나메인쿼리는서브쿼리의속성사용불가 Nested Loop, Sort Merge, Hash Join 모두사용가능 Semi Join 은 Exists Subquery 사용시 Filter 와같이첫번째만족하는조건을만나면즉시리턴함. Anti Join 은일반 join 과달리값이동일하지않은 row 를탐색함. Sub Query 사용시 Optimizer 가 Semi Join 및 Anti Join 을적용함. Sub Query 상에 use_nl, use_merge, use_hash 사용 35

36 Semi Join(Nested Loop Join) -예제 > 실행계획 select b.deptno, b.dname from dept b where exists ( select 1 from emp a where a.deptno = b.deptno ) order by b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem SORT ORDER BY :00: (0) 2 NESTED LOOPS SEMI :00: TABLE ACCESS FULL DEPT :00: * 4 INDEX RANGE SCAN IX_EMP_N :00:

37 Semi Join(Sort Merge Join) -예제 > 실행계획 select a.empno, a.ename, a.deptno from emp a where exists ( select 1 from dept b where a.deptno = b.deptno ) order by a.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem MERGE JOIN SEMI :00: TABLE ACCESS BY INDEX ROWID EMP :00: INDEX FULL SCAN IX_EMP_N :00: * 4 SORT UNIQUE :00: (0) 5 TABLE ACCESS FULL DEPT :00:

38 Semi Join(Hash Join) -예제 > 실행계획 select b.deptno, b.dname from dept b where exists ( select /*+ use_hash(a) */ 1 from emp a where a.deptno = b.deptno ) order by b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem SORT ORDER BY :00: (0) * 2 HASH JOIN SEMI :00: K (0) 3 TABLE ACCESS FULL DEPT :00: INDEX FULL SCAN IX_EMP_N :00:

39 Hash Join Right (Semi/Anti) Join > 발생조건 > 특징 > 적용 Semi 조인에서서브쿼리가소량일경우 Hash 조인과함께서브쿼리가먼저 Driving 되는조인방식 Semi 조인은항상메인쿼리가수행된후서브쿼리의데이터가존재하는지를체크하는방식 따라서서브쿼리는항상후행집합이될수밖에없음. 하지만 10g 부터 Plan 상에 Hash Join Right (Semi/Anti/Outer) 이나오게되면서브쿼리가 Driving 집합이됨. 서브쿼리집합이메인쿼리의집합보다적을때 10g 버전부터사용가능 39

40 Hash Join Right Semi - Concept >Full Hash Right Semi Full select a.empno, a.sal from big_emp a where exists (select /*+ use_hash(b) h(b) */ DEPT b.deptno from dept b where b.deptno = a.deptno ) ; HASH TABLE BIG_EMP 서브쿼리집합은 BUILD INPUT 이될수없으나 10g 부터 HASH JOIN RIGHT(SEMI/ANTI) JOIN 을이용하면가능함 BUILD INPUT TABLE PROBE TABLE 40

41 Hash Join Right Semi -예제 > 실행계획 select a.empno, a.sal from big_emp a where exists (select b.deptno from dept b where b.deptno = a.deptno ) ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 HASH JOIN RIGHT SEMI :00: K (0) 2 TABLE ACCESS FULL DEPT :00: TABLE ACCESS FULL BIG_EMP :00: Predicate Information (identified by operation id): access("a"."deptno"=to_number("b"."deptno")) 41

42 Anti Join(Nested Loop Join) -예제 > 실행계획 select b.deptno, b.dname from dept b where not exists ( select 1 from emp a where a.deptno = b.deptno ) order by b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem SORT ORDER BY :00: (0) 2 NESTED LOOPS ANTI :00: TABLE ACCESS FULL DEPT :00: * 4 INDEX RANGE SCAN IX_EMP_N :00:

43 Anti Join(Sort Merge Join) -예제 > 실행계획 select a.empno, a.ename, a.deptno from emp a where not exists ( select /*+ use_merge(b) */ 1 from dept b where a.deptno = b.deptno ) order by a.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem MERGE JOIN ANTI :00: SORT JOIN :00: (0) 3 TABLE ACCESS FULL EMP :00: * 4 SORT UNIQUE :00: (0) 5 TABLE ACCESS FULL DEPT :00:

44 Anti Join(Hash Join) -예제 > 실행계획 select b.deptno, b.dname from dept b where not exists ( select /*+ use_hash(a) h( */ 1 from emp a where a.deptno = b.deptno ) order by b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem SORT ORDER BY :00: (0) * 2 HASH JOIN ANTI :00: K (0) 3 TABLE ACCESS FULL DEPT :00: INDEX FULL SCAN IX_EMP_N :00:

45 Hash Join Right Anti - Concept >Full Hash Right Anti Full select a.empno, a.sal from big_emp a where not exists (select /*+ use_hash(b) h(b) */ DEPT b.deptno from dept b where b.deptno = a.deptno ) ; HASH TABLE BIG_EMP 서브쿼리집합은 BUILD INPUT이될수없으나 10g부터 HASH JOIN RIGHT(SEMI/ANTI) JOIN을이용하면가능함 전체건을 Scan 한다음 DEPT 테이블에없는건만조인됨 BUILD INPUT TABLE PROBE TABLE 45

46 Hash Join Right Anti -예제 > 실행계획 select a.empno, a.sal ; from big_emp a where not exists (select b.deptno from dept b where b.deptno = a.deptno ) Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 HASH JOIN RIGHT ANTI :00: K (0) 2 TABLE ACCESS FULL DEPT :00: TABLE ACCESS FULL BIG_EMP :00: Predicate Information (identified by operation id): access("a"."deptno"=to_number("b"."deptno")) 46

47 Index Join > 발생조건 > 특징 > 적용 테이블액세스없이하나이상의인덱스들을결합하여쿼리를수행 사용된모든컬럼이어떤인덱스에라도존재 비교연산자가 Equal( = ) 이아니어도인덱스조인에참여가능 반드시인덱스의선두칼럼이아니어도인덱스조인에참여가능 조건절을기준으로인덱스조인을결정 실행계획상에서는 Hash Join 으로수행되는것으로보임 검색에유리한인덱스가없을때인덱스머지를통해검색범위를줄이고자할때 > Hint /*+ index_join( 테이블명또는테이블별칭 ) */ 47

48 Index Join - 예제 > 실행계획 인덱스 Lay out 1 : JOB 인덱스 Lay out 2 : mgr + empno select /*+ index_join(emp) */ empno, job, mgr from emp where job = 'CLERK' and mgr = 7788 ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 VIEW index$_join$_ :00: * 2 HASH JOIN :00: K (0) * 3 INDEX RANGE SCAN IX_EMP_N :00: * 4 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): filter(("mgr"='7788' AND "JOB"='CLERK')) 2 - access(rowid=rowid) 3 - access("job"='clerk') 4 - access("mgr"='7788') 48

49 Outer Join > 특징 > 적용 Nested Loop, Sort Merge, Hash, Full Outer Join 형태 Nested Loop, Sort Merge, Hash Join 은앞장정리부분참조 Full Outer Join은첫번째집합을기준으로 Outer Join을하고, 두번째집합을기준으로 Anti 조인을한다음 union all Operation 을적용한다. 아우터조인되어있는집합에대응되는로우가없더라도기준집합의모든로우들을리턴하는조인 조인및 where 조건컬럼뒤에 (+) 기호로발생 49

50 Outer Join(Nested Loop) - 예제 > 실행계획 select a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno(+) ; Id Operation Name Starts E-Rows A-Rows A-Time Buffers NESTED LOOPS OUTER :00: TABLE ACCESS FULL EMP :00: * 3 TABLE ACCESS FULL DEPT :00: Predicate Information (identified d by operation id): filter("a"."deptno"=to_number("b"."deptno")) ( 50

51 Outer Join(Sort Merge) - 예제 > 실행계획 select /*+ use_merge(a b) */ a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno(+) ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem MERGE JOIN OUTER :00: SORT JOIN :00: (0) 3 TABLE ACCESS FULL EMP :00: * 4 SORT JOIN :00: (0) 5 TABLE ACCESS FULL DEPT :00: Predicate Information (identified by operation id): access("a"."deptno"=to_number("b"."deptno")) filter("a"."deptno"=to_number("b"."deptno")) 51

52 Outer Join(Hash) - 예제 > 실행계획 select /*+ use_hash(a h( b) */ a.empno, a.ename, b.dname from emp a, dept b where a.deptno = b.deptno(+) ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 HASH JOIN OUTER :00: K (0) 2 TABLE ACCESS FULL EMP :00: TABLE ACCESS FULL DEPT :00: Predicate Information (identified d by operation id): access("a"."deptno"=to_number("b"."deptno")) ( 52

53 Outer Join(Full Outer) - 예제 > 실행계획 select 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 VIEW :00: UNION-ALL :00: NESTED LOOPS OUTER :00: TABLE ACCESS FULL EMP :00: * 5 TABLE ACCESS FULL DEPT :00: NESTED LOOPS ANTI :00: TABLE ACCESS FULL DEPT :00: * 8 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): filter("a"."deptno"=to_number("b"."deptno")) 8 - access("a"."deptno"=to =TO_NUMBER( NUMBER("B" B."DEPTNO")) 53

54 Outer Join(Full Outer) - 예제 > Query Transformation select a.empno, a.ename, b.dname from emp a full outer join dept b on (a.deptno= b.deptno) ; select empno, ename, dname 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 ) 54 Query Transformation 실행계획이동일함 Id Operation Name VIEW 2 UNION-ALL 3 NESTED LOOPS OUTER 4 TABLE ACCESS FULL EMP * 5 TABLE ACCESS FULL DEPT 6 NESTED LOOPS ANTI 7 TABLE ACCESS FULL DEPT * 8 INDEX RANGE SCAN IX_EMP_N

55 Hash Join Right Outer > 발생조건 > 특징 > 적용 Outer Join 이 Hash Join으로풀리는경우 (+) 기호가붙은칼럼의테이블이아주작은테이블일경우 Driving 으로수행됨 Outer Join 시 9i 버전까지는무조건 (+) 기호가붙지않은칼럼의테이블이 Driving 되었음 10g 부터 (+) 기호가붙어있더라도소량의테이블이라면 Driving 될수있게됨 Hash Join Right (Semi/Anti) 와같은방식임 10g 버전부터사용가능 > Hint /*+ use_hash( 테이블명혹은 alias) */ 55

56 Hash Join Right Outer -예제 >Full Hash Right Outer Full select /*+ use_hash(a b) */ a.dname, b.empno, ename from dept a, big_emp b where a.deptno(+) = b.deptno ; DEPT HASH TABLE BIG_EMP (+) 기호가붙은쪽테이블은 BUILD INPUT 이될수없으나 10g 부터 HASH JOIN RIGHT OUTER JOIN 을이용하면가능함 BUILD INPUT TABLE PROBE TABLE 56

57 Hash Join Right Outer -예제 > 실행계획 select /*+ use_hash(a b) */ a.dname, b.empno, ename from dept a, big_emp b where a.deptno(+) = b.deptno ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem * 1 HASH JOIN RIGHT OUTER :00: K (0) 2 TABLE ACCESS FULL DEPT :00: TABLE ACCESS FULL BIG_EMP :00: Predicate Information (identified by operation id): access("a"."deptno"="b"."deptno") 57

58 Partition Outer Join > 개념 년별또는월별집계데이터를나타낼때특정년혹은특정월에데이터가없더라도생성하여나타냄. 주로 OLAP 에서많이활용함 요구사항 테이블구조 Select yymm From Year_month Where yymm = 2002 Select deptno, yymm, sale_amt from dept_sale_history 58

59 Partition Outer Join -예제 > 기존방식 SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e ON (m.yymm = e.yymm AND e.deptno = 10) WHERE m.yymm like '2002% Union all SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e ON (m.yymm = e.yymm AND e.deptno = 20) WHERE m.yymm like '2002% ; > 해결방법 SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e PARTITION BY (e.deptno) ON (m.yymm = e.yymm ) WHERE m.yymm like '2002% ; 59

60 Partition Outer Join -예제 > 기존방식 SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e ON (m.yymm = e.yymm AND e.deptno = 10) WHERE m.yymm like '2002% Union all SELECT e.deptno, m.yymm, NVL(e.sale_amt,0) FROM year_month m LEFT OUTER JOIN dept_sale_history e ON (m.yymm = e.yymm AND e.deptno = 20) WHERE m.yymm like '2002% ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem UNION-ALL :00: * 2 HASH JOIN OUTER :00: K (0) * 3 TABLE ACCESS FULL YEAR_MONTH :00: * 4 TABLE ACCESS FULL DEPT_SALE_HISTORY :00: * 5 HASH JOIN OUTER :00: K (0) * 6 TABLE ACCESS FULL YEAR_MONTH :00: * 7 TABLE ACCESS FULL DEPT_SALE_HISTORY :00: Predicate Information (identified by operation id): access("m"."yymm"="e"."yymm") 3 - filter("m"."yymm" LIKE '2002%') 4 - filter(("e"."deptno"=10 AND "E"."YYMM" LIKE '2002%')) 5 - access("m"."yymm"="e"."yymm") 6 - filter("m"."yymm" (""" " LIKE '2002%')') 7 - filter(("e"."deptno"=20 AND "E"."YYMM" LIKE '2002%')) 60

61 Partition Outer Join -예제 > 해결방법 SELECT e.deptno, m.yymm, mm NVL(e.sale_amt,0) amt FROM year_month m LEFT OUTER JOIN dept_sale_history e PARTITION BY (e.deptno) ON (m.yymm = e.yymm ) WHERE m.yymm like '2002% ; Id Operation Name Starts A-Rows A-Time Buffers Used-Mem VIEW :00: MERGE JOIN PARTITION OUTER :00: SORT JOIN :00: (0) * 4 TABLE ACCESS FULL YEAR_MONTH :00: * 5 SORT PARTITION JOIN :00: (0) 6 TABLE ACCESS FULL DEPT_SALE_HISTORY :00: Predicate Information (identified by operation id): filter("m"."yymm" LIKE '2002%') 5 - access("m"."yymm"="e"."yymm") filter("m"."yymm"="e"."yymm") 61

62 Star Query Transformation > 개념 > 제약사항 > Hint 소량의데이터를가진여러개의디맨젼테이블과팩트테이블의개별비트맵인덱스를이용하여처리범위를줄이는조인방식 B-TREE 인덱스와는다르게독립적인 BIT MAP INDEX 로 LIKE, BETWEEN 등범위검색에서도 BIT MAP 인덱스머지가일어남. 내부적으로옵티마이져가질의를변형하여실행계획을생성함. FROM 절에여러 FACT 테이블이조인된것을 WHERE 절에서브쿼리조인으로바꿈. 하나의팩트테이블에최소한 2 개이상의디맨션테이블이있어야한다. 팩트테이블의외부키에는반드시비트맵인덱스가존재해야한다. 팩트테이블에반드시통계정보가생성되어있어야한다. 파라메터 (STAR_TRANSFORMATION_ENABLED) 가 TRUE 이어야함. 바인드변수를사용하면안된다.( 반드시상수인경우에발생됨 ) /*+ STAR_TRANSFORMATION */ 62

63 Star Query Transformation -예제 > STAR_TRANSFORMATION 예제 create bitmap index idx_sales_n01 _ on sales(sale_date); create bitmap index idx_sales_n02 on sales(cust_id); alter session set star_transformation_enabled _ = true; Select /*+ STAR_TRANSFORMATION */ A.QUTER, B,STATE,, SUM(C.AMOUNT) FROM CARENDAR A, CUSTOMER B, SALES C WHERE A.SALE_DATE = C.SALE_DATE AND B.CUST_ ID = C.CUST_ ID AND B.STATE = CA AND A.QUTER = Query Transformation SELECT A.QUTER, B,STATE, SUM(C.AMOUNT) FROM CARENDAR A, CUSTOMER B, SALES C WHERE SALES_DATE IN (SELECT SALES_DATE FROM CARENDAR WHERE QUTER = ) AND CUST_ID IN (SELECT CUST_ID FROM CUSTOMER WHERE STATE = CA ) 63

64 Oracle Data Join Method- 정리 Nested Loop Join Sort Merge Join Hash Join Cartesian Join ( 혹은 Cross Join) Sub Query (=,In, Any, All, Exists, Subquery Factoring) Semi Join (Nested Loop, Sort Merge, Hash, Hash Join Right) Anti Join (Nested Loop, Sort Merge, Hash, Hash Join Right) Index Join Outer Join (Full,Nested Loop, Sort Merge, Hash, Hash Join Right) Partition Outer Join Star Query Transformation 64

65 Special Key Note Oracle Data Join Method 감사합니다. 65