DB 오라클 - 자주 사용하는 힌트절

 

이번 글에서는 간단히 자주 사용되는 오라클 힌트 절에 대해서 작성해 볼 것이다.

'개발자를 위한 인덱스 생성과 SQL 작성 노하우(이병국)'을 참고하여 작성하였다. 

DB에서 옵티마이저는 최적의 실행계획을 결정한다. 하지만 그렇다고 해서 옵티마이저가 만능이라는 것은 아니다. 옵티마이저도 잘못된 SQL이나 부정확한 통계정보로 인하여 실수도 할 수 있다. 이럴 때는 힌트 절을 통해 잘못된 실행계획을 바로 잡을 수 있다. 즉, 힌트 절은 옵티마이저의 실수를 만회할 수 있는 용도로 사용할 수 있는 것이다. 

옵티마이저에 대한 내용은  https://khdscor.tistory.com/m/52 를 참고하길 바란다.

데이터베이스 옵티마이저에 대한 간단 설명

 

 

그렇다고 옵티마이저가 힌트 절을 무조건 수용하지는 않는다. 말도 안 되는 힌트 절은 무시할 것이다.

 

모든 힌트절은 아래의 형식으로 작성한다. 

 

SELECT /*+ [힌트절] */ ~

 

 

힌트 절은 주석 문 안에 작성하기에 잘못 작성해도 에러가 날 일은 없다. 에러가 나도 힌트 절이 실행이 안되기만 할 뿐이다

힌트 절의 종류는 매우 많지만 아래에서는 간단하게 7가지만 작성하겠다.

 

접근 순서를 결정하는 힌트 절

1. ORDERED:

FROM절에 나열된 테이블 순서대로 접근한다(SQL 문에 종속적). 

일반적으로 옵티마이저는 여러 테이블의 조인 시 인덱스가 있는 방향으로 접근한다. 그렇기에 동일한 쿼리라도 인덱스 생성 포인트에 따라 테이블 접근 방향은 가변적이다. 하지만 만약 모든 테이블에서 접근을 위한 인덱스가 생성돼 있다면 옵티마이저는 더 비용이 적게 드는 접근 방향을 설정할 것이다. 하지만 옵티마이저가 계산한 방식과 개발자가 판단한 방식하고 다르다면 난해할 것이다. 이럴 때 ORDERED 힌트 절을 통해 FROM에 있는 테이블 순서대로 원하는 데로 접근 경로를 설정할 수 있는 것이다.  사용 예시는 아래와 같다. 

 

SELECT /*+ ORDERED */ 컬럼들~
FROM 고객 A, 고객, B
WHERE A.고객번호 = B.고객번호
AND A.고객명 = ?
AND B.주문일자 = ?

 

 

2. LEADING:

이 힌트 절도 원하는 순서로 테이블 접근 경로를 설정할 수  있도록 하는 힌트 절이다.

LEADING는 ORDERED와는 다르게 순서를 직접 명시한다. 예시는 아래와 같다. 

 

SELECT /*+ LEADING */ 컬럼들~ 
FROM 고객 A, 고객, B 
WHERE A.고객번호 = B.고객번호 
AND A.고객명 = ? 
AND B.주문일자 = ?

 

 

위와 같이 접근 경로를 결정하는 힌트 절을 작성할 때 여러 개의 테이블이 얽혀있다면 아래의 3가지 기준을 참고하면 좋을 것 같다.

1. 진입형 테이블을 결정한다: 조건 중에서 조회 범위가 자근 테이블을 우선함
2. 연결 확장형 보다는 연결 축소형 테이블을 우선한다: 조회 범위가 점점 줄어드는 JOIN을 우선함
3. OUTER JOIN보다는 INNER JOIN을 우선한다: INNER JOIN은 조회 범위 축소 가능

 

 

접근 방법을 결정하는 힌트절

접근 방법을 결정하는 힌트 절은 테이블 간 접근 방법을 결정하는 힌트 절이다. 이러한 힌트 절을 통해 Nested Loop Join, Hash Join, Sort Merge Join 등의 조인 방식을 결정할 수 있다. 

일반적으로 OLTP는 Nested Loop Join이 많이 사용되고 Sort Merge Join은 거의 사용되지 않으며 Hash join은 배치성 쿼리, OLAP에 많이 사용된다. 

3. USE_NL: 

Nested Loop Join을 하도록 한다. 사용 시 'USE_NL(A, B, C, D)'  와 같이 작성을 하는데 이는 A, B, C, D로 접근할 때 Nested Loop Join을 하도록 한다는 것이다. 예시는 아래와 같다. 

 

SELECT /*+ USE_NL(B) */ 컬럼들~ 
FROM 고객 A, 고객, B 
WHERE A.고객번호 = B.고객번호 
AND A.고객명 = ? 
AND B.주문일자 = ?

 

 

4. USE_HASH:

Hash Join을 하도록 한다. 사용시 'USE_HASH(A, B, C)'와 같은 형식을 작성하는데 이는 A, B, C로 접근 시 Hash Join을 한도록 한다는 것이다. 예시는 아래와 같다. 

 

SELECT /*+ USE_HASH(B) */ 컬럼들~ 
FROM 고객 A, 고객, B 
WHERE A.고객번호 = B.고객번호 
AND A.고객명 = ? 
AND B.주문일자 = ?

 

 

자원 사용을 결정하는 힌트 절

5. INDEX: 

하나의 테이블에 여러 개의 인덱스가 존재할 때 어느 인덱스를 사용할지를 지정해주는 힌트 절이다. 

이 힌트 절을 통해 ORDER BY 절을 사용하는 대신 이미 정렬된 인덱스를 사용함으로써 성능 향상을 이룰 수도 있다. 사용 예시는 아래와 같다. 

 

SELECT /*+ INDEX(주문 고객번호_IDX) */ 컬럼들~
FROM 주문
WHERE 고객번호 = ? 
AND 주문일자 = ?

 

 

위는 주문 테이블에서 고객 변호_IDX 인덱스를 사용한다는 의미이다. 참고로 아래와 같은 인덱스 관련 힌트 절도 있다. 

1. INDEX_SS: 결합 인덱스의 선행컬럼 조건이 입력되지 않을 때 사용한다(INDEX SKIP SCAN)
2. INDEX_FFS: 인덱스만을 빠르게 전체 스캔한다(INDEX FAST FULL SCAN)
3. INDEX_DESC: 인덱스를 통해 테이터를 역순으로 스캔한다. 

 

6, 7. FULL, PARALLEL:

일반적으로 인덱스를 사용해서 테이블을 조회하지만 테이블 탐색범위가 너무 넓을 때에는 인덱스를 사용하면 랜덤 액세스 부하가 너무 크게 나올 것이다. 그렇기에 테이블 풀스캔을 사용할 때가 더 좋은 성능을 기대할 수도 있다. 그럴 때 사용하는게 이 힌트 절이다. 

FULL 힌트 절에 덧붙여서 PARALLEL 힌트 절을 사용할 수 있다. 병렬 처리를 위한 힌트 절이므로 처리 성능은 매우 좋으나, 자원을 독점적으로 사용하므로 멀티 유저 환경에서는 주의해야 한다. 만약 수치 값을 1로 주면, FULL 힌트절만 작동할 것이다. 또한 수치값을 주지 않는다면 사용 가능한 자원 모두를 사용하므로 주의해야 하나. 일반적으로 4 정도를 준다.

병렬 처리에 대한 내용은 https://jack-of-all-trades.tistory.com/198 를 참고하길 바란다.

오라클 병렬처리(Parallel Processing) 개념 및 용어 정리, 종합페이지

 

 사용 예시는 아래와 같다. 

 

SELECT /*+ FULL(주문) PARALLEL(주문 4)  */  컬럼들~
FROM 주문
WHERE 주문일자 BETWEEN ? AND ?
ORDER BY 주문번호

 

 

위의 힌트 절 중 USE_HASH, FULL, PARALLEL 3가지 힌트 절은 배치성 퀴리, 즉 대용량 데이터 처리와 조회에 빈번하게 사용하는 힌트 절이다. 이중 PARALLEL은 자원을 독점적으로 사용하므로 신중하게 사용해야 한다. 

 

 

참고

개발자를 위한 인덱스 생성과 SQL 작성 노하우 - 이병국

https://jack-of-all-trades.tistory.com/198

오라클 병렬처리(Parallel Processing) 개념 및 용어 정리, 종합페이지