[Oracle] 결합 컬럼 인덱스 / 조인(Join)문의 종류와 성능

1. 결합 컬럼 인덱스를 생성할 때 선행 컬럼을 결정하는 기준에 대해 자세히 설명하시오.

ex.1) CREATE INDEX I_EMP_DATE_DEPTNO ON BIG_EMP(HIREDATE, DEPTNO)

ex.2) CREATE INDEX I_EMP_DATE_DEPTNO ON BIG_EMP(DEPTNO, HIREDATE)

SELECT DEPTNO, COUNT(*), SUM(SAL)

FROM BIG_EMP

WHERE HIREDATE BETWEEN TO_DATE('1983-01-01','YYYY-MM-DD')

AND TO_DATE('1984-12-31','YYYY-MM-DD')

AND (DEPTNO = 70 OR DEPTNO = 80 OR DEPTNO = 90)

GROUP BY DEPTNO

ORDEY BY SUM(SAL) DESC

위 두 가지 인덱스를 적용한 후 실행한 SELECT문의 처리과정과 실제로 READ해야 하는 BLOCK의 개수는 크게 차이가 난다. 원인으로는 불필요한 검색범위가 줄고 이에 따라 실행계획이 변경이 되기 때문이다. 이처럼 결합 컬럼 인덱스를 생성할 때 정하는 DRIVING COLUMN이 어떤 컬럼이 되느냐에 따라 실행하는 SQL문의 성능에 크게 영향을 미칠 수 있는 요소이다. 경우에 따라서는 생성한 인덱스를 두고 FTS 스캔을 하는 경우도 발생할 수 있는 등 성능과 관련되어서는 반드시 고려해야 할 요소임에 틀림없다.

- WHERE절에서 자주 검색되는 선행 컬럼이 되어야 함.

- 분포도가 좋은 컬럼이 선행 컬럼이 되어야 함.

: 분포도 = 조건을 만족하는 행수/전체행수 * 100

보통 10% 내외가 가장 이상적

- 데이터양이 적은 컬럼을 선행으로 사용.

- BETWEEN, > AND <, LIKE 연산자로 검색하지 않는 컬럼을 선행 컬럼으로 사용.

2. 조인문의 종류에 대해 설명하고 조인문의 성능에 영향을 주는 요소와 작성 방법에 대해 설명하시오.

SORT-MERGE JOIN

NESTED-LOOPS JOIN

CLUSTER JOIN

HASH JOIN

1. SORT-MERGE JOIN

두 개 이상의 테이블이 공통 컬럼을 기준으로 결합 될 때(EQUI-JOIN), 각각의 컬럼에 인덱스가 전혀 없는 상태에서 조인이 되는 방법. FTS(FULL TABLE SCAN)에 의한 검색이기 때문에 성능이 매우 떨어짐.

2. NESTED-LOOPS JOIN

두 개 이상의 테이블이 공통 컬럼을 기준으로 결합 될 때(EQUI-JOIN), 둘 다 혹은 둘 중 하나의 컬럼에 인덱스가 존재하는 검색방법. 가장 보편적으로 사용됨. 데이터 스캔 범위를 줄일 수 있어 SORT-MERGE JOIN보다 빠른 성능을 기대할 수 있음.

3. HASH JOIN

두 개 이상의 테이블이 논리적으로 결합될 때(EQUI-JOIN) 공통 컬럼을 기준으로 메모리 상에서 여러개의 파티션 영역을 나누어 결합하는 조인 기법. 일반적으로 HINT(USE_HASH)를 사용하여 처리하고, 주로 처리량이 많은 전체범위를 처리하는 경우에 사용하면 효율적이다. 이에 따라 효과적인 검색 및 결합이 되기 위해서는 인덱스 구성이 중요함.

4. CLUSTER JOIN

2개 이상의 테이블이 자주 조인되어 실행될 때, 일반적인 조인문의 실행과정처럼 블록으로부터 각각 읽혀져서 결합되는 것이 아닌, 처음부터 블록구조 내에 조인되어 결합된 형태로 저장되는 기법.

조인문 성능에 영향을 주는 요소

- DRIVING TABLE(선행 테이블)

조인에 참여하는 각 테이블은 데이터의 양과 분포도가 다르기 때문에 일반적으로

데이터 범위가 좁은 테이블을 선행 테이블로 결정하는 것이 성능상 유리함.

1. 조인에 참여하는 컬럼에 모두 인덱스가 있거나 / 모두 없는 경우(동등조건)

: 가장 오른쪽에 배치된 테이블이 선행 테이블로 결정됨.

2. 개발자에 의해 조인 순서를 결정하는 경우

: 옵티마이저가 최적의 실행계획을 결정하지 못하는 경우에 사용함.

- 테이블의 조인 순서

1. 조인에 참여하는 테이블들의 인덱스의 종류에 따라서 실행 순서가 바뀔 수 있다.

2. 구동테이블이 결정되고 나면, 나머지 테이블들 중 순서가 결정이 되는데 우선순위

가 적용이 된다.

: 모두 인덱스가 존재하는 경우(싱글<결합, NON-UNIQUE<UNIQUE)

- 검색되는 테이블의 데이터량에 따라 성능이 좌우

: 넓은 범위가 아닌 좁은 범위 / FTS보다 INDEX SCAN

- 조인 시, INDEX의 사용여부

: 동일한 조인문장이더라도, 인덱스가 존재하지 않는 SORT-MERGE JOIN과

NESTED LOOPS JOIN의 결과는 다른 성능을 나타나게 됨. 일반적으로 INDEX

사용한 NESTED-LOOPS 조인이 성능이 우수하지만, 경우에 따라 SORT-MERGE JOIN

이 유리한 경우도 있다.