'SQL Driving'에 해당되는 글 1건


Driving Table 과 JOIN

   

   

  • Driving Table
    • 가장먼저 Access되는 Table 로 Join 문장을 구성 시에 중요한 요소
    • Hint 를 사용하여 Triving Table 을 지정 가능
    • Optimizer Mode 가 Cost Base 일 경우에는 통계정보가 우선하나 일반적으로 FROM 절에서 앞 Table 이 Driving Table 이 됨.
    • Rule Base 일 경우에는 동일 조건일 때 FROM 절에서 뒤 Table 을 Optimizer 가 Access 하게 됨.

         

         

      화면 캡처: 2011-12-29 오후 5:24

         

  • Join 의 원리
    • Join 이란 테이블간에 물리적 관계가 없어도 논리적 관계만으로 원하는 정보를 서로 연결하여 참조할 경우에 사용
    • Join 시 수행속도를 향상하려면 Access 횟수를 줄이는 것이 기본이자 원리
    • 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법 이다.
    • 보통 둘 이상의 행들의 공통된 값 Primary Key 및 Foreign Key 값을 사용하여 조인 한다.
    • 그러므로 두 개의 테이블을 SELECT문장 안에서 조인 하려면 적어도 하나의 컬럼이 그 두 테이블 사이에서 공유 되어야 한다.

       

  1. Join 의 종류

   

  1. Equi, non_Equi, self Join

       

  • Cartesian Product(카티션 곱)

    - 검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블들의 모든 데이터가 반환 되는 현상

       

    - Cartesian Product는 다음과 같은 경우에 발생 한다.

    * 조인 조건을 정의하지 않았을 경우

    * 조인 조건이 잘못된 경우

    * 첫 번째 테이블의 모든 행들이 두 번째 테이블의 모든 행과 조인이 되는 경우

    * 테이블의 개수가 N이라면 Cartesian Product를 피하기 위해서는 적어도 N-1개의 등가 조건을 SELECT 문안에 포함시켜서 다른 테이블 안에 있는 각 테이블의 컬럼이 적어도 한번은 참조되도록 해야 한다.

  1. Equi Join

    - 조건절 Equality Condition(=)에 의하여 조인이 이루 진다.

    - Equi join의 성능을 높이기 위해서는 Index 기능을 사용하는 것이 좋다.


 

-- WHERE 절에 조인 조건을 작성한다.
SQL>
SELECT e.ename, d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno;

   

   

화면 캡처: 2011-12-28 오후 5:25

   

   

화면 캡처: 2011-12-28 오후 5:27

   

   

  1. Non-Equi Join
  • - Non-equi Join은 테이블의 어떤 column도 Join할 테이블의 column에 일치하지 않을 때 사용하고, 조인조건은 동등( = )이외의 연산자를 갖는다.
  • BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN

  

-- Non-Equi Join 예제
SQL>

SELECT e.ename,e.sal,s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

   

   

   

화면 캡처: 2011-12-28 오후 5:26

   

   

화면 캡처: 2011-12-28 오후 5:28

   

   

  1. Self Join
  • - Equi Join과 같으나 하나의 테이블에서 조인이 일어나는 것이 다르다.
  • - 같은 테이블에 대해 두 개의 alias를 사용하여 FROM절에 두 개의 테이블을 사용하는 것 처럼 조인한다.

 

-- 사원의 매니저명을 조회하는 Self Join 예제
SQL>
SELECT e.ename, a.ename "Manager"
FROM emp e, emp a
WHERE e.empno = a.mgr;

   

   

화면 캡처: 2011-12-28 오후 5:29

   

   

   

화면 캡처: 2011-12-28 오후 5:29

   

  1. Nested Loop Join

       

    - Driving Table 의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐 아니라 테이블간의 연결도 순차적임.

    - 선행적 특징을 갖으며, 먼저 엑세스되는 테이블의 처림범위에 의해 처리량이 결정됨.

       

  • Nested Loop Join 의 원리

       

 SQL 문

 SELECT A.COL1, B.COL2

    FORM TAB1 B, TAB2 A

  WHERE B.KEY2 = A.KEY1

       AND A.KEY = 'AA'

       AND A.COL1 = '11'

       AND B.COL2 = '22';

   

   

 INDEX 정보

테이블 명 : 인덱스명  : 컬럼구성

TAB2 A    : TAB2_PK : KEY1

TAB1 B    : TAB1_PK : KEY2

   

   

실행계획

   

 1. INDEX TAB2PK ACCESS 후 ROWID 추출

 2. 테이블 TAB2 ACCESS

 3. NESTED LOOP JOIN 으로 INDEX TAB1_PK를 ACCESS

 4. ROWID 로 테이블 TAB1 을 읽음

   

  

   

  • Nested Loop Join 의 특징

       

 순차적

 Driving Table 의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐 아니라 테이블간의 연결도 순차적임

 선행적

 먼저 엑세스되는 테이블의 처림범위에 의해 처리량이 결정

 종속적

 나주엥 처리도는 테이블은 앞서 처리된 값을 받아 엑세스함. 즉 값을 받아서 처리범위가 정해짐

 RANDOM ACCESS

 Driving Table 의 인덱스 엑세스는 첫 번째 로우만 Random Access 이고, 나머지는 Scan, 연결작업은 Random Access

 선택적

 연결되는 방향에 따라 사용되는 INDEX 들이 달라질 수 있음.

 연결고리중요, 방향성

 연결고리의 인덱스 유무에 따라 엑세스 바향 및 수행 속도에 차이 발생

 부분범위처리가능

 연결작업 수행 후 Check 되는 조건으로 부분범위처리를 하는 경우 조건의 범위가 넓거나 없다면 더 빨라짐

   

  • Nested Loop Join 의 사용

       

 부분범위처리를 하는 경우

- 전체가아닌 부분범위 처리하는 경우에 유리

 Join 되는 테이블이 상호의존적인 경우

- Join 되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아서 처리량을 줄일 수 있을 경우 유리

- Driving Table 의 처리가 많거나 연결 테이블의 Random Access 량이 많을 경우 Sort/Merge Join이 유리

 처리량이 적은 경우

- Random Access 를 많이 하므로 On-Line 애플리케이션 같이 처리량이 적은 경우에 유리

 Driving Table 의 선택이 관건

- 어느 테이블이 먼저 Access 되는가가 수행속도에 큰 영향을 미침

   

   

  1. Sort Merge Join

       

  • Sort Merge Join 의 원리

    - 일반적으로 Batch 나 DW작업 처리시 사용하며 전체적인 자료를 처리하는 경우에 주로 사용

    - Eqil-join 에 대해서만 사용가능

    - 사용시 Optimizer 에 HINT(USE_MERGE)를 사용하여 처리

    - 각각의 테이블에 대하여 Sort 처리 후 Merge 를 하는 Join

       

   

 SQL 문

 SELECT /** USE_MERGE(B A) */ A.COL1, B.COL2

    FORM TAB1 B, TAB2 A

  WHERE B.KEY2 = B.KEY1

       AND A.KEY = 'AA'

       AND A.COL1 = '11'

       AND B.COL2 = '22';

   

 INDEX 정보

테이블 명 : 인덱스명  : 컬럼구성

TAB2 A    : TAB2_PK : KEY1

TAB1 B    : TAB1_PK : KEY2

   

실행계획

   

 1. INDEX TAB2PK ACCESS 후 ROWID 추출

 2. 테이블 TAB2 ACCESS 한 결과를 SORT

 3. 테이블 TAB1 을 FTS(Full Table Scan) 한 후 SORT

 4. 두 테이블 결과를 MERGE

   

   

  • Sort Merge Join 의 특징

       

동시적

각각의 테이블이 자신의 처리범위를 엑세스하여 정렬해 둠

독립적

각 테이블은 다른 테이블에서 어떤 상수값도 제공받지 않고 주어진 상수값에 의해서만 범위를

전체범위처리

부분범위를 사용할 수 없음

Scan방식

자신의 처리범위를 줄이기 위해 index 를 사용하는 경우에만 Random Access 하고 Merge 작업은 Scanq 방식사용

선택적

연결고리가 되는 컬럼은 인덱스를 사용하지 않음

무방향성

Join 의 방향과 무관

   

  • Sort Merge Join 의 사용

       

전체범위처리를 하는 경우

- Sort/Merge Join 은 주로 부분이 아닌 전체범위를 처리하는 경우에 유리

Hash Join이 수행 하지 못하는 EQUI-JOIN 일 때 주로 사용, 그 외의 경우는 모두 Hash Join을 사용한다.

Join 되는 테이블이 상호독립적인 경우

- 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있을 경우 유리함

- 상수값을 받아 줄여진 범위가 30%이상이면 Sort/Merge Join 이 유리

처리량이 많은 경우

- Random Access 를 하지 않으므로 전체범위처리에 유리

효과적인 인덱스 구성이 관건

- 자신의 처리범위를 어떻게 줄이느냐가 관건이므로 효과적인 인덱스 구성이 중요

   

   

   

   

  1. Hash Join

       

    - Oracle8 Server 는 Cost-Based 옵티마이저를 사용하는 경우만 Hash Join 을 고려

    - Equi-Join 에 대해서만 사용가능

    - 일반적으로 HINT(USE_HASH) 를 사용하여 처리

    - HASH FUNCTION 을 이용해서 메모리와 CPU를 많이 사용해서 대용량 데이터를 처리할 경우에 사용

       

  • Hash Join 의 원리

       

 SQL 문

 SELECT /** USE_HASH(B A) */ A.COL1, B.COL2

    FORM TAB1 B, TAB2 A

  WHERE B.KEY2 = B.KEY1

       AND A.KEY = 'AA'

       AND A.COL1 = '11'

       AND B.COL2 = '22';

   

   

 INDEX 정보

테이블 명 : 인덱스명  : 컬럼구성

TAB2 A    : TAB2_PK : KEY1

TAB1 B    : TAB1_PK : KEY2

   

   

실행계획

   

  

 1. INDEX TAB2PK ACCESS 후 ROWID 로 테이블 TAB2 ACCESS

 2. 테이블 TAB1 을 FTS(Full Table Scan)

 3. 두 테이블 결과를 HASH JOIN

   

  

   

  • Hash Join 의 특징

       

선택적

연결고리되는 컬럼의 index를 사용하지 않음

독립적

각 테이블은 다른 테이블에서 어떤 상수값도 제공받지 않고 주어진 상수값에 의해서만 범위를

전체범위처리

부분범위를 사용할 수 없음

Sort 안 함

SORT 를 하지 않으므로 SORT MERGE JOIN 보다는 좋은 성을을 내며, 작은 테이블과 큰 테이블의 JOIN 시에 유리

   

   

  1. Outer Join

   

   

   

Outer join 예)

   

예제1) 일반 조인의 경우

  • Outer Join을 했을 경우

   

SQL> SELECT DISTINCT(a.deptno), b.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno

  

   

   

   

화면 캡처: 2011-12-28 오후 5:34

   

   

   

화면 캡처: 2011-12-28 오후 5:35

   

   

SQL> SELECT DISTINCT(a.deptno), b.deptno
FROM emp a, dept b
WHERE a.deptno(+) = b.deptno

  

   

   

   

화면 캡처: 2011-12-28 오후 5:42

   

   

   

화면 캡처: 2011-12-28 오후 5:42

   

예제2)

   

   

   

화면 캡처: 2011-12-28 오후 5:48

   

첫번째 쿼리 결과를 잘 보면 Outer Join이 되지 않은 것을 알 수 있다.

  • Outer Join 조건이 걸려있는 테이블에는 다른 조건절이 들어와도 똑같이 Outer Join 연산자인 (+)를 해주어야 한다.

   

그 외 Outer Join..

   

  • 표준 Outer Join
  • Oracle9i 부터는 ANSI/ISO SQL 표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 한다.
  • LEFT OUTER JOIN과 RIGHT OUTER JOIN의 테이블 순서를 바꾸어 가면서 테스트를 하시면 쉽게 이해를 할 수 있다.

   

  1. LEFT OUTER JOIN
  • 오른쪽 테이블(아래 예제에서 emp 테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

 

SQL> SELECT DISTINCT(e.deptno), d.deptno
FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno;

  1. RIGHT OUTER JOIN
  • 왼쪽 테이블(아래 예제에서 emp 테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.


 

SQL> SELECT DISTINCT(e.deptno), d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

  1. FULL OUTER JOIN
  • 양쪽 테이블에 다 Outer Join을 거는것을 TWO-WAY OUTER JOIN 또는 FULL OUTER JOIN이라 한다.


 

SQL>SELECT DISTINCT(a.deptno), b.deptno
FROM emp a FULL OUTER JOIN dept b ON a.deptno = b.deptno;

   

   

  1. 그 외 Join들….

       

    1. CROSS JOIN

Cartesian Product 값을 얻을때 사용 한다.


 

-- 아래 SQL문장을 실행해 보자
SQL> SELECT ename
FROM emp CROSS JOIN dept

  1. INNER JOIN

    - 일반 조인시 ,(콤마)를 생략하고 INNER JOIN을 추가하고, WHERE절 대신 ON절을 사용하면 된다.

    - INNER는 생략 가능 하다.

    - 아래 두 조인의 결과 값은 같다.

       


 

-- INNER JOIN을 사용한 문장
SQL> SELECT e.empno, e.ename
FROM dept d INNER JOIN emp e ON d.deptno=e.deptno;


 

-- 일반적인 SQL 문장
SQL> SELECT e.empno, e.ename
FROM dept d , emp e WHERE d.deptno=e.deptno;

  1. NATURAL JOIN

    - Equi Join과 동일 하다고 보면 된다.

    - 두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인이 된다.

    - 동일한 컬럼을 내부적으로 찾게 되므로 테이블 Alias를 주면 오류가 발생 한다.

    - 동일한 컬럼이 두개 이상일 경우 JOIN~USING 문장으로 조인되는 컬럼을 제어 할 수 있다.

    - 아래 두 조인의 결과 값은 같다.


 

-- NATURAL JOIN을 사용한 SQL 문장.
SQL> SELECT empno, ename, deptno FROM emp NATURAL JOIN dept


 

-- 일반적인 SQL 문장
SQL> SELECT e.empno, e.ename, d.deptno
FROM emp e, dept d WHERE e.deptno=d.deptno

  1. JOIN ~ USING

    - NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되는데, USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있다.

    - USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 발생 한다.


 

-- 일반적인 사용 방법
SQL> SELECT e.empno, e.ename, deptno
FROM emp e JOIN dept d USING(deptno)

  1. ON 구문

    - 조인 조건을 지정 할 수 있다.

    - 모든 논리 연산 및 서브쿼리를 지정할 수 있다.


 

-- 테스트를 위해 scott유저에서 아래 insert문장을 실행시킨다.
SQL> INSERT INTO bonus(ename, job, sal) VALUES('SMITH','CLERK',500);

-- ON절 사용 예제 (multi-table joins)
SQL> SELECT e.empno, e.ename, e.sal
FROM emp e JOIN dept d ON (e.deptno=d.deptno)
JOIN bonus b ON (b.ename = e.ename)
WHERE
e.sal IS NOT NULL

   

  • 부분범위 처리

   

  1. 부분범위 처리과정

   

   

  1. Union All

    - Union 이란 자신과 다른 집합의 합집합을 구할 경우에 사용하며 결과과 Unique 해야 함

    ☞ 결과가 Unique 하기 위해서는 전체를 Sort 한 후 중복을 제거하는 과정을 거치므로 전체 처리범위를 엑세스하게 된다.

   

  • Union All

    ☞ Oracle7에서 추가된 기능

    ☞ 중복을 확인하지 않고 부분범위로 처리가 가능

    ☞ Union 사용시에는 두 결과가 중복되지 않는다면 Union All 로 변경해도 결과가 같으몰 전체범위 처리를 부분범위 처리로 변경할 수 있음

   

  1. Exists

    - Sub Query 란 Main Query 내에 SQL Query 를 기술하는 것

    ① 어떤 조건을 마족하는 집합여부를 확인 하는 것은 Exists 문을 활용하여 처리

    ② Exists 는 주어진 조건을 만족하는 첫 번째 레코드를 만나면 트랜젝션을 멈추게 됨

    ③ 전체범이가 아닌 부분범위 처리를 할 수가 있음.

   

   

  • Join 의 최적경로

   

1. 가장 효율적인 Join 경로는 논리적으로 이미 존재함. 이것을 단지 Optimizer 가 찾아 줄 뿐, 없는 경로를 새롭게 생성해 주는 것이 아님

   

2. Index, Cluster 등의 Optimizing Factor 에 대한 지정 방법을 통해 어떤 것이 최적의 경로인지를 알고 있어야 하며 과연 Optimizer 가 정확하게 그 경로를 찾아주는지를 판단할 수 있어야 함

   

3. 파단기준으로 Optimizer 는 Ranking 이라는 것을 사용하게 돔.

   

4. 특이한 경우의 처리를 위해서 Hint 나 사용제한 기능(Suppessing) 을 이용하여 Optimizer 를 지산이 원하는 엑세스 경로로 유도할 수 있어야 함

   

   

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 10g Documentation Library - councepts

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.oracleclub.com/lecture/1035>

원본 위치 <http://blog.daum.net/dmz7881/8873046>

원본 위치 <http://blog.daum.net/_blog/hdn/ArticleContentsView.do?blogid=0LDC4&articleno=4653786&looping=0&longOpen=>

'1. IT Story > DB' 카테고리의 다른 글

Oracle EXPLAIN PLAN/DBMS_XPLAN PACKAGE  (0) 2012.02.29
Oracle SQL과 옵티마이져  (0) 2012.02.29
Oracle SQL Sub-query(기초)  (0) 2012.02.29
Oracle HINT(기초)  (0) 2012.02.29
Oracle SQL INDEX(기초)  (0) 2012.02.29
Oracle SQL Trace[기초]  (0) 2012.02.29
아카이브 로그 모드 Archive Log Mode  (0) 2012.02.29
Oracle TableSpace  (0) 2012.02.29
블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,