▣ 아웃터 조인시 In 조건의 해결 (대용량II 3-34 ~ 3-37)
1. 아웃터 조인의 개념
Outer조인은 어떤 집합을 기준으로 해서 조인되는 다른 집합과의 연결에 실패했더라도 그 결과를 추출하는 조인을 말한다.
1) 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
2. 아웃터 조인의 실패
아웃터 조인시 연결 조건에 (+)기호를 붙여줘야 한다. 아래와 같은 SQL을 살펴보자
1) 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And B.CON = ‘10’
이것을 수행하면 Tab1와 Tab2가 아웃터 조인 되고 그 결과 중 CON=10 이라는 조건을 만족하는 것만 결과로 나오게 된다.
따라서 우리가 원하는 대로 Tab1의 모든 데이터가 나오게 되지 않는다.
3. 아웃터 조인의 실패 해결 방안
이러한 문제를 해결하기 위해서 연결 조건과 (+)기호를 붙인 쪽의 다른 조건들에 대해서도 (+)기호를 붙이면 이러한 문제를 해결 할 수 있다.
1) 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And B.CON(+) = ‘10’
4. 아웃터 조인시 (+)기호가 붙은 쪽에 In이나 Or 조건이 있는 경우 위에서 제시한 해결책을 사용한 아래와 같은 SQL을 살펴보자.
아래 SQL을 실행하려고 하면 ORA-01719: outer join operator (+) not allowed in operand of OR or IN 이라는 에러 메시지를 만나게 된다.
1) 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And B.CON(+) in (‘10’,’20’)
5. 아웃터 조인시 (+)기호가 붙은 쪽에 In이나 Or 조건이 있는 경우의 해결책
(1) 대용량에서 설명에 사용하는 방법
1) or is null 사용하기
☞ 제약조건 및 한계
① 컬럼의 Not Null이 보장되어야 한다.
- Null값을 가진 데이터가 조인에 성공하지 않아도 포함되게 된다.
② In조건으로 들어온 값을 가진 Row만 조인에 성공해야 한다.
- In조건으로 들어온 값 외의 값을 가진 것 중 조인에 성공한 것이 누락된다.
☞ 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And (B.CON in (‘10’,’20’) or B.CON is null)
상수조건부분 (B.CON in (‘10’,’20’) or B.CON is null) 이 Filter로 처리되므로 우리가 원하는 결과와 달라지게 된다.
2) Inline View 사용하기
☞ 제약조건 및 한계
① 선행집합의 조건을 받지 못해서 처리 범위가 넓어질 수 있다.
☞ 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A,
( SELECT NO, CON
FROM Tab2 B
WHERE B.CON in (‘10’,’20’) ) B
Where A.NO = B.NO(+)
(2) 또 다른 해결책들
DECODE와 BETWEEN은 (+)기호를 붙일 수 있다는 점에 착안하여 다음과 같이 해결 할 수 있다.
1) Bewteen 사용하기(값을 Between으로 표현이 가능한 경우)
☞ 제약조건 및 한계
① In조건으로 들어온 값이 연속되어 있거나 사이에 다른 값이 없어야 한다.
즉, Between으로 표현이 가능해야 하며, 표현방식을 바꿔도 다른 값이 끼어
들지 않아야 한다.
☞ 장점
① 조건을 받는 컬럼에 인덱스가 걸려 있으면 인덱스 사용이 가능하다.
☞ 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And B.CON(+) BETWEEN ‘10’ AND ’20’
3) Bewteen 사용하기(값 사이에 다른 값이 있는 경우)
☞ 제약조건 및 한계
① Or is null의 경우와 같이 조인 후 걸러지므로 결과 값이 달라진다.
☞ 예제 SQL
SELECT NO1, NO2, CON
FROM (
SELECT A.NO NO1, B.NO NO2, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And B.CON(+) BETWEEN ‘10’ AND ’50’
)
WHERE B.CON in (‘10’,’20’,’50’)
or B.CON is null
2) Decode 사용하기
☞ 장점
① In조건으로 들어온 값이 연속되지 않아도 사용이 가능하다.
② Filter로 처리되지 않는다.
③ 결합인덱스 후행 컬럼일 경우 인덱스 체크조건으로 사용된다.
☞ 예제 SQL
SELECT A.NO, B.NO, B.CON
FROM Tab1 A, Tab2 B
Where A.NO = B.NO(+)
And DECODE(B.CON(+), ‘10’,’1’,’20’,’1’) = ‘1’
▣ 컬럼 Decode 사용과 인덱스 사용
1) 인덱스 선두 컬럼인 경우
인덱스 선두 컬럼인 경우 Decode가 사용되면 인덱스를 사용하지 못한다.
2) 인덱스 후행 컬럼인 경우
인덱스 선행 컬럼에 조건이 들어오고 후행 컬럼이 Decode로 가공되어도 해당
컬럼에 부여된 조건이 인덱스가 엑세스 될 때 작업량을 줄여주는 조건으로 사용
된다.
(3) 결 론
or is null / Inline View 를 사용할 경우 결과값이 달라질 수 있거나 조건을 받지 못해서 처리 범위가 넓어지는 경우가 발생한다.
이런 것들 보완하기 위해서 Bewteen / Decode 를 사용할 수 있는데 Bewteen 은 값이 연속된 경우만 사용이 가능하며
그것을 보완하기 위해서 Bewteen 과 Inline View 를 같이 사용할 경우 결과 값이 달라질 가능성이 있으므로 Decode 를 사용하는 것이 가장 좋다.