개발자를 위한 인덱스 생성과 SQL작성 노하우 : 15-17

 

- 유용한 오라클 힌트절

 

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

• ORDERED : FROM 절에 나열된 테이블 순서대로 접근한다.(SQL문에 종속적)
• LEADING : 테이블 접근 순서를 명시적으로 표현한다.(SQL문에 독립적)

 

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

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

 

ORDERED 힌트절은 FROM절에 나열된 테이블 순으로 접근을 유도하지만, LEADING 힌트절은 테이블 접근 순서를 명시적으로 표시한다. LEADING 힌트절은 FROM절에 종속적이지 않기 때문에 ORDERED 힌트절보다 개선된 힌트절이다.

 

힌트절을 사용할 때 테이블 접근 순서에 대한 결정 기준

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

 

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

• USE_NL : NESTED LOOP JOIN 방식으로 조인하도록 유도한다.(순차적인 접근 방법)
• USE_HASH : HASH JOIN 방식으로 조인하도록 유도한다,(해시 함수를 이용한 접근 방법)

 

SELECT /*+ USE_NL(B) */
              컬럼들, ...
FROM   고객 A, 주문 B
WHERE A.고객번호 = B.고객번호
AND       A.고객명 = '홍길동'
AND       B.주문일자 = '20220801'

 

1. 고객 테이블에서 고객명이 '홍길동'인 고객을 구한다(선행 테이블 결정)
2. '홍길동' 고객의 수만큼 순차적으로 주문 테이블을 고객번호 컬럼으로 접근한다(순차적 접근)
3. 주문 테이블에서 주문일자가 '20220801'인 정보만 필터한다.

 

고객 테이블에서 주문 테이블로 순차적으로 접근하고, 진행 방향인 고객의 고객명 컬럼과 주문의 고객번호 컬럼에 인덱스가 존재해야 한다.

 

SELECT /*+ USE_HASH(B) */
               A.조직명, A.조직코드, SUM(B.실적), ...
FROM    조직 A, 집계 B
WHERE A.조직코드 = B.조직코드
AND       A.사업부 = '강원사업부'
AND       B.집계년월 = '202208'
GROUP BY A.조직명, A.조직코드

 

1. 조직 테이블에서 사업부가 '강원사업부'인 조직들을 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 분류한 다음, 해시 테이블을 생성한다(해시 함수를 이용해 해시 테이블을 구성)
2. 집계 테이블에서 집계년월이 '202208'인 자료를 구한 후, 조인절 컬럼인 조직코드를 해시 함수로 변환 후 해시 테이블로 순차적으로 접근한다(해시 함수를 통해 해시 테이블을 탐색)

 

해시 함수를 이용해 두 테이블을 조인하는 것으로, 조회 조건 컬럼의 인덱스는 사용되고, 조인 컬럼에는 인덱스가 존재하더라도 사용되지 않는다.

 

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

• INDEX : 인덱스를 통한 접근 경로를 유도한다.
• FULL : 테이블을 풀스캔한다.
• PARALLEL : 병렬 처리를 통해 성능을 높인다.

 

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

 

INDEX_SS : 결합인덱스의 선행컬럼 조건이 입력되지 않을 때 사용한다(INDEX SKIP SCAN)

INDEX_FFS : 인덱스만을 빠르게 전체 스캔한다(INDEX FAST FULL SCAN)

INDEX_DESC : 인덱스를 통해 데이터를 역순으로 스캔한다.

 

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

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

 

PARALLEL 힌트절(테이블명 병렬처리수치)은 FULL 힌트절과 같이 사용된다. 병렬 처리를 위한 힌트절이므로 처리 성능은 매우 좋으나, 자원을 독점적으로 사용하므로 멀티 유저 환경에서는 주의해야 한다. 만약 병렬 처리 수치값을 1로 주면 FULL 힌트절만 작동한다. 또한 수치값을 주지 않으면 사용 가능한 자원을 모두 사용하므로 주의해야 한다.

 

USE_HASH, FULL, PARALLEL 힌트절은 배치성 쿼리에서 많이 사용된다. 대용량 데이터 처리와 조회에 빈번하게 사용하는 힌트절로 사용 여부를 검토해서 적절하게 사용하는 것이 좋다.

1. USE_HASH 힌트절만 사용해서 조회 가능한지 검토 : 적당히 무거운 쿼리에 사용
2. 조회 범위가 크다면 FULL 힌트절 추가 사용을 검토 : 대개 이 단계에서 튜닝 완료
3. 대용량 데이터의 빠른 처리가 요구 될 때 PARALLEL 힌트절 사용 : 제한적 사용

 

- NULL

 

오라클에서 NULL은 길이가 0인 문자열과 동일한 의미를 갖거나 알 수 없는 미지의 값을 의미한다.

 

1. 사칙연산에서의 NULL

사칙연산에서 NULL 계산 결과는 항상 NULL이다.

SQL > SELECT NULL + 3 FROM DUAL        -------        결과 : NULL
SQL > SELECT NULL - 3 FROM DUAL        -------        결과 : NULL
SQL > SELECT NULL * 3 FROM DUAL        -------        결과 : NULL
SQL > SELECT NULL / 3 FROM DUAL        -------        결과 : NULL

 

2. 비교연산에서의 NULL

NULL은 어떤 비교를 하든 그 결과값은 NULL이다.

번호	기준금액	목표금액
1	100	200
2	100	NULL
3	NULL	200
4	NULL	NULL

SQL > SELECT 번호 FROM 실적 WHERE 기준금액 = 목표금액    ---    결과 : 없음
SQL > SELECT 번호 FROM 실적 WHERE 기준금액 > 목표금액    ---    결과 : 없음
SQL > SELECT 번호 FROM 실적 WHERE 기준금액 < 목표금액    ---    결과 : 1
SQL > SELECT 번호 FROM 실적 WHERE 기준금액 <> 목표금액    ---    결과 : 1

 

3. 집계함수에서의 NULL

집계함수에서 NULL값은 제외되고 처리된다.

번호	주문금액
1	200
2	400
3	NULL

SQL > SELECT SUM(주문금액) FROM 주문    ---   결과 : 600
SQL > SELECT AVG(주문금액) FROM 주문     ---   결과 : 300
SQL > SELECT MAX(주문금액) FROM 주문    ---   결과 : 400
SQL > SELECT MIN(주문금액) FROM 주문     ---   결과 : 200

 

4. 문자열 결합에서의 NULL

표준 SQL에서 NULL과 문자열의 결합은 NULL이지만, 오라클의 문자열 결합에서의 NULL은 길이가 0인 문자열('')과 동일하게 인식된다.

SQL> SELECT NULL || 'ABC' FROM DUAL          ---    결과 : 'ABC'
SQL> SELECT NULL ||3 FROM DUAL                  ---    결과 :  3
SQL> SELECT NULL || 'ABC' || 3 FROM DUAL    ---    결과 : 'ABC3'

 

5. 논리연산에서의 NULL

논리연산자는 AND(논리곱), OR(논리합), NOT(부정)이 있다.

NULL AND TRUE         -- 결과 : NULL
NULL AND FALSE       -- 결과 : FALSE(둘 중에서 하나만 거짓이면 거짓)
NULL OR TRUE          -- 결과 : TRUE(둘 중에서 하나만 참이면 참)
NULL OR FASLE         -- 결과 : NULL
NOT(NULL)                 -- 결과 : NULL

 

6. 인덱스에서의 NULL

인덱스가 있는 컬럼이라도 조건절에서 IS NULL 또는 IS NOT NULL 구문 사용시 인덱스를 사용하지 못하고 해당 테이블을 풀스캔한다. 그 이유는 인덱스가 기본적으로 NULL 정보를 보관하지 않기 때문이다.

 

IS NULL을 사용하지 않고 컬럼의 최초 저장 데이터가 NULL인 컬럼을 조회할 수 있는 방법은?

 

첫째, NULL 회피 전략이 있다. 해당 컬럼에 NULL값을 사용하지 않고 의미 있는 다른 특별한 값을 사용한다. 예를 들어 컬럼이 배송일자인 경우 NULL 대신 '99991231'이라는 접해 볼 가능성이 전혀 없는 미래 날짜를 사용하는 것이다.

 

둘째, 함수 기반 인덱스(Function Based Index)를 이용하는 것이다. 컬럼에 함수까지 포함시켜서 인덱스를 생성한다. 인덱스는 NULL 값을 보관하지 않지만, 함수 기반 인덱스는 함수를 이용해 변환된 값을 보관하기 때문에 가능하다. 조회할 때 IS NULL 대신 NVL2(배송일자, 배송일자, '99991231') = '99991231' 형태로 조회한다.

 

IS NOT NULL을 사용하는 경우에는 BETWEEN 구문을 이용해 NULL 사용을 대체할 수 있다.

 

7. 검색에서의 NULL

WHERE 컬럼 IS NULL                            --     결과 : 올바른 NULL 검색 조건
WHERE 컬럼 IS NOT NULL                   --     결과 : 올바른 NULL 검색 조건
WHERE 컬럼 = NULL                             --     결과 : 틀린 NULL 검색 조건
WHERE 컬럼 <> NULL                           --     결과 : 틀린 NULL 검색 조건
WHERE 컬럼 = 'NULL'                           --      결과 : 문자열 'NULL' 검색 조건
WHERE 컬럼 LIKE '%NULL%'               --      결과 : 문자열 'NULL' 검색 조건
WHERE 컬럼 IN (NULL)                         --      결과 : 틀린 NULL 검색 조건
WHERE 컬럼 IN ('NULL')                       --      결과 : 문자열 NULL 검색 조건

 

8. 함수에서의 NULL

NVL, NVL2는 NULL관련 함수로, 해당 컬럼의 값이 NULL이면 특정 값으로 치환해야 하는 경우 사용한다.

NVL(컬럼, NULL이면 치환할 값)
NVL2(컬럼, NULL이 아니면 치환할 값, NULL이면 치환할 값)

 

NVL함수는 집계함수와 사용할 때 주의해야 한다.

번호	주문금액
1	200
2	400
3	NULL

 

SQL > SELECT SUM(주문금액) FROM 주문                                   --                         결과 : 600
SQL > SELECT SUM(NVL(주문금액, 0)) FROM 주문                      --                         결과 : 600
SQL > SELECT NVL(SUM(주문금액), 0) FROM 주문                      --                         결과 : 600

 

첫 번째 SQL문은 올바르게 사용했으나, 집계할 레코드가 없는 경우 NULL값을 리턴한다.

두 번째 SQL문은 잘못된 사용법으로, 집계함수에서는 NULL값이 있는 레코드는 제외하고 계산하기 때문에 NVL 사용이 의미가 없다. 오히려 레코드 수만큼 NVL 함수를 호출함에 따라 부하가 발생한다.

세 번째 SQL문은 올바른 사용법으로, 집계할 레코드가 없다면 0값을 리턴한다. NVL함수는 최종 집계 결과에 대해서 한 번만 호출된다.

 

SQL > SELECT AVG(주문금액) FROM 주문                                   --                         결과 : 300
SQL > SELECT AVG(NVL(주문금액, 0)) FROM 주문                      --                         결과 : 200
SQL > SELECT NVL(AVG(주문금액), 0) FROM 주문                      --                         결과 : 300

 

첫 번째 SQL문은 올바르게 사용했으나, 집계할 레커드가 없는 경우 NULL값을 리턴한다.

두 번째 SQL문은 잘못된 사용법으로, NULL값이 있는 레코드가 0으로 치환돼 분자의 값은 변함이 없으나 분모의 값이 크게 만들어서 평균값이 낮아지는 결과를 초래한다.

세 번째 SQL문은 올바른 사용법으로, 집계할 레코드가 없으면 0을 리턴한다.

 

9. 조인에서의 NULL

OUTER JOIN에서 연결되지 않는 레코드의 값은 NULL이다.

 

- GATHER_PLAN_STATISTICS 힌트절

 

GATHER_PLAN_STTISTICS 힌트를 이용하면 SQL Trace를 수행하지 않고도 쿼리의 실행계획 단계별로 Get Block을 알 수 있다. 쿼리 성능을 확인/비교할 수 있으므로 튜닝할 때 빈번하게 사용되는 힌트절이다.

 

SELECT /*+ GATHER_PLAN_STATISTICS */
               *
FROM 인사
WHERE 사용자명 = '홍길동';

SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ASLLSTATS LAST'))

대상 쿼리를 실행한 후에 분석 쿼리를 곧바로 수행해야 한다. 왜냐하면 GATHER_PLAN_STATISTICS 힌트절을 추가해 실행한, 가장 최근의 쿼리에 대한 수행 정보를 보여주는 분석 쿼리이기 때문이다.

 

Id, Operation, Name 
실행계획 정보로, 자원에 대한 접근 순서와 접근 방법을 나타낸다. 접근 순서를 변경 할 수 있는 힌트절은 ORDERED, LEADING이 있다. 또한 접근 방법을 변경할 수 있는 힌트절은 USE_NL, USE_HASH, USE_MERGE가 있다. 

Starts
오퍼레이션을 수행한 횟수를 의미한다. Starts * E-Rows 의 값이 A-Rows 값과 비슷하다면, 통계정보의 예측 로우 수와 실제 실행 결과에 따른 실제 실제 수가 유사함을 알 수 있다. 만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 볼 수 있고, 이로 인해 오라클의 Optimizer가 잘못된 실행 계획을 수립할 수도 있다.

E-Rows (Estimated Rows)
통계정보에 근거한 예측 Row 수를 의미한다. 통계정보를 갱신할수록 값이 매번 다를 수 있으며, 대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없다. 하지만 E-Rows 값과 A-Rows 값이 현격하게 차이가 있다면 오라클이 잘못된 실행 계획을 세울 수도 있음을 인지해야 하며, 통계정보 생성을 검토해야 한다.

A-Rows (Actual Rows)
쿼리 실행 결과에 따른 실제 Row 수를 의미한다. A-Rows 에서 중요한 여러 정보를 추정 할 수 있다.

A-Time (Actual Elapsed Time)
쿼리 실행 결과에 따른 실제 수행 시간을 의미한다. 하지만 실행 시점의 여러 상황이 늘 가변적이고 또한 메모리에 올라온 Block의 수에 따라서 수행 시간이 달라지므로 해당 값에 큰 의미를 둘 필요는 없다.

Buffers (Logical Reads) 
논리적인 Get Block 수를 의미한다. 해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로, 튜닝을 진행할 때 가장 중요하게 생각되는 요소 중 하나다.

Reads (Physical Reads)
물리적인 Get Block 수를 의미한다. 동일한 쿼리를 여러 번 수행할 때 처름에는 값이 있으나, 처음이 아닌 경우에는 값이 0인 것을 보면 알 수 있듯이 메모리에서 읽어온 Block은 제외된다. 해당 값에 큰 의미를 둘 필요는 없다.

튜닝 시 가장 중요하게 활용되는 부분은 Buffers와 A-Rows다. Buffers 값을 통해서 Get Block의 총량을 알 수 있고, A-Rows를 통해 실행계획 단계별로 실제 Row 수를 알 수 있기 때문이다.

 

테이블 접근 순서와 접근 방법에 따른 성능 비교

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) */
*
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

테이블 접근 순서(인사 -> 실적), 접근 방법(For문 형식의 순차적 접근)

 

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(B A) */
*
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

테이블 접근 순서(실적 -> 인사),&nbsp;&nbsp;접근 방법(For문 형식의 순차적 접근)

 

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(A B) USE_HASH(B) */
*
FROM 인사 A, 실적 B
WHERE A.인사번호 = B.영업자번호
AND A.사용자명 = ‘이슬기’
AND B.영업일자 = ‘20150223’;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

테이블 접근 순서(인사 -> 실적), 접근 방법(Hash함수를 이용한 접근)

 

접근순서 : 인사 → 실적, 접근방법 : For문 형식의 순차적 접근 : 14 Buffers
접근순서 : 실적 → 인사, 접근방법 : For문 형식의 순차적 접근 : 393 Buffers
접근순서 : 인사 → 실적, 접근방법 : Hash 함수를 이용한 접근 : 29 Buffers

동일한 쿼리라도 어떤 순서대로 테이블을 접근하느냐 혹은 어떤 방법으로 접근하느냐에 따라서 오라클 옵티마이저가 일한 총량이 달라진다.