4. 조인 순서의 중요성
1. 필터 조건이 없을 때
다른 필터 조건이 없는 상황에서는 작은 쪽 집합을 Outer 테이블로 하는 것이 유리하다.
2. 필터 조건이 있을 때
조인 조건 외에 필터 조건이 있을 때는 인덱스 구성에 따라 유,불리가 정해지며, 인덱스 구성이 좋다면 작은 쪽 집합을 Outer 테이블로 하는 것이 유리하다.
3. 소트 머지 조인 / 해시 조인의 경우
-
소트 머지 조인
소트 머지 조인은 PGA 상에 정렬된 집합을 통해 조인 액세스가 일어나기 때문에 Random 액세스 발생량 보다는 소트 부하에 의해 성능이 결정된다.
디스크 소트가 발생할 정도의 큰 테이블을 포함할 때는 큰 테이블을 Outer 테이블로 두는게 더 빠르지만, 메모리 소트 방식으로 조인할 때는 작은 쪽 테이블을 Outer 테이블로 두는 게 조금 더 빠르다. -
해시 조인
해시 조인은 Hash Area에 Build Input을 모두 채울 수 있느냐 없느냐가 관건이므로 작은 쪽 테이블을 Outer 테이블로 두는 것이 유리하다.
5. Outer 조인
1. Outer NL 조인
NL 조인은 Outer 조인할 때 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블이 항상 Outer 테이블로 선택된다.
leading 이나 ordered 힌트를 사용해서 순서를 바꾸려고 해도 소용없다.
NL 조인이 가장 조인 순서에 영향을 많이 받기 때문에 불필요한 Outer 조인이 발생하지 않도록 주의해야 한다.
ERD 표기를 따르는 SQL 개발의 중요성
ERD 표기에서 점선일 때는 모든 레코드가 출력되야 할 수 있어 반대 테이블에 Outer 기호(+) 를 반드시 붙여줘야한다.
반대로, 실선일 때는 모든 레코드를 출력되도록 하려고 굳이 Outer 기호(+)를 붙일 필요가 없다.
필수 컬럼이 있으면 테이블을 생성할 때 NOT NULL 제약을 걸어야 한다.
NULL 값을 두려워해 습관적으로 Outer 기호(+) 를 붙인다면 성능상 불이익이 생길 수 있다.
2. Outer 소트 머지 조인
Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer 기호(+) 가 붙지 않은 테이블이 항상 Outer 테이블로 지정된다.
힌트로도 순서를 바꿀 수 없다.
3. Outer 해시 조인
오라클 9i까지 Outer 해시 조인도 방향이 고정됐었다.
Outer 기호(+) 가 붙지 않은 테이블이 Build Input 이 되는 것이다.
해시 조인은 주로 대용량 테이블을 조인할 때 사용하는데 Outer 조인할 때 순서가 고정되다 보니 자주 성능 문제를 일으켰다.
오라클은 이 문제를 해결하려고 10g 에서 Right Outer 해시 조인을 도입하게 되었다.
4. Full Outer 조인
Ansi Full Outer 조인이 나타나기 전엔 (오라클 9i) "Left Outer 조인" + Union All + Anti 조인(Not Exists) 방식으로 Full Outer 조인을 구현했다.
1. ANSI Full Outer 조인
오라클 9i 부터는 ANSI Full Outer 조인을 제공했지만, 실행계획은 이전과 같게 나타났다.
쿼리문은 간단해졌지만 테이블을 각각 두 번씩 액세스하는 비효율은 같았다.
2. Native Hash Full Outer 조인
오라클 11g부터 'Native Hash Full Outer 조인'을 보였고 _optimizer_native_full_outer_join 을 조정해 이 기능을 사용할 수 있다.
이때 부터는, 각각의 테이블을 한 번씩만 액세스한다.
3. Union All을 이용한 Full Outer 조인
Union All 을 이용하면 버전과 상관없이 각 테이블에 한 번씩만 액세스가 일어난다.
조인 대신 sort group by 나 hash group by 연산을 수행한다.
6. 스칼라 서브쿼리를 이용한 조인
1. 스칼라 서브쿼리
쿼리에 내장된 또다른 블록을 서브쿼리라 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 '스칼라 서브쿼리' 라고 한다.
스칼라 서브쿼리는 Outer 조인과 100% 같은 결과를 낼 수 있다.
즉 스칼라 서브쿼리에서 실패하는 레코드들은 null값으로 출력된다.
2. 스칼라 서브쿼리의 캐싱 효과
오라클은 스칼라 서브쿼리 수행횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(UGA 영역)에 저장해 둔다.
캐시 내부에서 입력 값을 찾으면 출력 값을 리턴하고 없으면 쿼리를 수행해 결과를 저장해 둔다.
입력 값은 조건절이고 출력 값은 쿼리의 컬럼 값이다.
입력 값과 출력 값을 빠르게 찾고 저장하기 위해 오라클은 해싱 알고리즘을 사용한다.
스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 효과가 있다.
게다가 스칼라 서브쿼리를 이용하면 NL 조인에서 inner 쪽 인덱스와 테이블에 나타나는 버퍼 Pinning 효과도 사라진다.
두 개 이상의 값을 리턴하고 싶을 때
스칼라 서브쿼리는 한 레코드당 하나의 값만 리턴한다는 특성때문에 그럴 수 없다.
이런 상황에서 어떻게 쿼리해야 효과적일까?
✅ 구하고 싶은 값을 모두 문자열로 합친 후 메인 쿼리에서 substr 함수를 사용해 분리한다.
✅ 오브젝트 TYPE을 사용한다.
