Oracle SQL Driving Table & JOIN(기초)

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