'오라클 드라이빙'에 해당되는 글 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 기능을 사용하는 것이 좋다.

   

   

화면 캡처: 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

   

   

   

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

   

   

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

   

   

1. Self Join

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

   

   

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

   

   

   

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

   

1. Nested Loop Join

      

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

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

      

• Nested Loop Join 의 원리

     

   

   

   

   

   

• Nested Loop Join 의 특징

     

   

• Nested Loop Join 의 사용

     

   

   

1. Sort Merge Join

      

• Sort Merge Join 의 원리

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

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

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

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

     

   

   

   

   

• Sort Merge Join 의 특징

     

   

• Sort Merge Join 의 사용

     

   

   

   

   

1. Hash Join

      

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

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

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

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

      

• Hash Join 의 원리

     

   

   

   

   

   

• Hash Join 의 특징

     

   

   

1. Outer Join

   

   

   

Outer join 예)

   

예제1) 일반 조인의 경우

• Outer Join을 했을 경우

   

   

   

   

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

   

   

   

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

   

   

   

   

   

화면 캡처: 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 테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

1. RIGHT OUTER JOIN

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

1. FULL OUTER JOIN

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

   

   

1. 그 외 Join들….

      

   1. CROSS JOIN

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

1. INNER JOIN

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

   - INNER는 생략 가능 하다.

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

      

1. NATURAL JOIN

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

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

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

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

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

1. JOIN ~ USING

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

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

1. ON 구문

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

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

   

• 부분범위 처리

   

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=>