조인 순서의 중요성

• 조인순서에 따라 쿼리 수행 성능이 달라질 수 있다.

• 고객Table 생성

CREATE TABLE 고객
AS
SELECT LEVEL "고객번호"
      ,MOD(LEVEL, 3) + 1 "납입방법코드"
      ,DECODE(MOD(LEVEL, 10)
      ,1, '서울', 2, '대전', 3, '대구', 4, '부산', 5, '찍고'
      ,6, '아하', 7, '경기', 8, '충청', 9, '전라', 0, '경상'
      ) "거주지역"
FROM  dual
CONNECT BY LEVEL <= 100000
;

ALTER TABLE 고객 ADD CONSTRAINT pk_고객 PRIMARY KEY(고객번호);

CREATE INDEX idx_1 ON 고객(거주지역);

CREATE INDEX idx_2 ON 고객(납입방법코드);

• 납입방법Table 생성

CREATE TABLE 납입방법
AS
SELECT 1 납입방법코드, '신용카드' 납입방법 FROM dual
UNION ALL SELECT 2, '자동이체' FROM dual
UNION ALL SELECT 3, '지로'     FROM dual
;

ALTER TABLE 납입방법
ADD CONSTRAINT pk_납입방법 PRIMARY KEY(납입방법코드);

필터조건이 없을 때

Case1) 고객Table을 드라이빙 했을 경우

SQL> SELECT /*+ LEADING(b) USE_NL(a) */
  2         a.납입방법, b.*
  3  FROM   납입방법 a, 고객 b
  4  WHERE  b.납입방법코드 = a.납입방법코드
  5  ;

• 고객Table에서 납입방법코드 인덱스로 조인 액세스 할때 10만번의 Random 액세스 발생!!
• 납입방법 Table로 액세스할 때 다시 10만 번의 Random 액세스 발생!!
• 총 20만번 발생!!

Case2) 납입방법Table을 드라이빙 했을 경우

SQL> SELECT /*+ LEADING(a) USE_NL(b) */
  2         a.납입방법, b.*
  3  FROM   납입방법 a, 고객 b
  4  WHERE  b.납입방법코드 = a.납입방법코드
  5  ;

• 납입방법Table에서 고객 쪽 납입방법코드 인덱스로 조인을 시도할 때 3번의 Random 액세스 발생!!
• 고객Table을 액세스할 때는 10만 번의 Random 액세스 발생!!
• 총 100,003번 Random 액세스 발생!!

다른 필터 조건이 없는 상황에서는 작은 쪽 집합을 드라이빙하는 것이 유리하다는 것을 알수 있다.

필터조건이 있을 때

• 거주지역이 '부산'인 고객만을 대상으로 조회 할 때
• 부산지역고객은 전체 중10%에 해당하는 1만 명
• 위에 Table생성 보면, 고객Table 거주지역 컬럼에 인덱스 만들어져 있음

Case1) 고객Table 드라이빙 했을 경우

SQL> SELECT /*+ LEADING(b) USE_NL(a) */
  2         a.납입방법, b.*
  3  FROM   납입방법 a, 고객 b
  4  WHERE  b.납입방법코드 = a.납입방법코드
  5  AND    b.거주지역     = '부산'
  6  ;

• 거주지역 인덱스를 통해 고객Table을 액세스할 때 1만번 Random 액세스 발생!!
• 납입방법코드 인덱스로 조인 액세스 할 때 1만번 Random 액세스 발생!!
• 납입방법Table을 액세스할 때도 1만번 Random 액세스 발생!!
• 총 3만번 Random 액세스 발생!!

Case2) 납입방법Table 드라이빙 했을 경우

SQL> SELECT /*+ LEADING(a) USE_NL(b) */
  2         a.납입방법, b.*
  3  FROM   납입방법 a, 고객 b
  4  WHERE  b.납입방법코드 = a.납입방법코드
  5  AND    b.거주지역     = '부산'
  6  ;

• 납입방법Table에서 고객 쪽 납입방법코드 인덱스로 조인을 시도할 때 3번의 Random 액세스 발생!!
• 고객Table을 액세스할 때는 10만 번의 Random 액세스 발생!!
• 총 100,003번 Random 액세스 발생!! 앞서 필더조건 없는 경우와 똑같은 일량을 한것을 알수 있다.

Case3) 납입방법Table 드라이빙 + 거주지역 인덱스 추가

SQL> SELECT /*+ LEADING(a) USE_NL(b) */
  2         a.납입방법, b.*
  3  FROM   납입방법 a, 고객 b
  4  WHERE  b.납입방법코드 = a.납입방법코드
  5  AND    b.거주지역     = '부산'
  6  ;

• Random 액세스가 10,003번으로 줄었다.

결론

1. 조인 조건 외에 필터 조건이 있을 때는 인덱스 구성에 따라 유리, 불리가 결정 된다.
2. 비효율이 없게끔 인덱스를 잘 구성해 주기만 한다면 역시 작은 쪽 집합을 드라이빙 하는것이 유리하다.
3. NL 조인에서는 Random 액세스 발생량 외에도 Inner 쪽 인덱스 구성 및 조건절 연산자 형태에 따라 성능이 크게 좌우된다.

소트 머지 조인과 해시 조인의 경우

소트 머지 조인
1. PGA상에 정렬된 집합을 통해 조인 액세스가 일어나기 때문에 Random 액세스 발생량보다는 소트 부하에 의해 성능이 결정
2. 디스크 소트가 발생할 정도의 큰 테이블을 포함할 때는 큰 테이블을 드라이빙 하는 것이 더 빠르다.
하지만, 메모리 소트 방식으로 조인할 때는 작은 쪽 테이블을 드라이빙 하는 것이 조금 더 빠르다.

해시조인
1. Hash Area에 Build Input을 모두 채울 수 있느냐가 관건이므로 두말 할 것도 없이 작은 쪽 테이블을 드라이빙 하는 것이 유리하다.