SQL튜닝이론 - 옵티마이저의 자동형변환으로 인해 발생하는 인덱스 스캔 성능저하

SQL튜닝이론 - 옵티마이저의 자동형변환으로 인해 발생하는 인덱스 스캔 성능저하

 

 

SQL에서 직접 인덱스 칼럼을 가공하지 않았음에도 옵티마이저가 형변환을 위해 자동으로 가공하는 경우가 있습니다. 옵티마이저가 인덱스 칼럼을 가공했기 때문에 INDEX RANGE SCAN을 할 수 없게 됩니다.

 

숫자형 칼럼과 문자형 칼럼 사이에서 발생하는 형변환

 

-- idx_고객 : 생년월일

SELECT * FROM 고객
WHERE 생년월일 = 19821225

-- Exectuion plan
-- SELECT STATEMENT
--        TABLE ACCESS(FULL) OF '고객'(TABLE)
-- Predicate Information
-- 1.filter(TO_NUMBER("생년월일") = 19821225

 

이는 고객 테이블의 생년월일 칼럼이 문자형인데 조건 절 비교값을 숫자형으로 표현했기 때문입니다. 오라클의 경우 조건절에서 칼럼과 값의 형이 맞지 않을 때 컴파일 에러를 내지 않고 자동으로 형변환합니다. 칼럼의 형에 따라 형변환 대상이 달라집니다. 위의 쿼리를 예로 숫자형 입력값과 문자형 칼럼이 만나면 문자형 칼럼을 숫자형으로 자동변환합니다.

 

날짜형 칼럼과 문자형 칼럼이 사이에서 발생하는 형변환

 

SELECT * FORM 고객
WHERE 가입일자 = '01-JAN-2018'

 

고객테이블의 가입일자 칼럼의 형식이 날짜형이라면 문자형을 날짜형으로 변환합니다. 이 경우 칼럼을 가공하는 것이 아닌 값 부분을 가공하기 때문에 인덱스 사용에는 문제가 없습니다. 하지만 스캔 성능에 문제가 없더라도 자동형 변환이 일어나게 해선 안됩니다. 아래와 같이 명시적으로 형을 지정해 주는 코딩습관이 필요합니다. 

 

SELECT * FROM 고객
WHERE 가입일자 = TO_DATE('01-JAN-2018', 'DD-MON-YYYY')

 

LIKE 연산에서 숫자형과 문자형의 형변환

 

일반적인 조건절에 숫자형과 문자형이 만나면 문자형을 숫자형으로 변환하지만 연산자가 LIKE 인 경우에는 반대로 적용됩니다. LIKE 연산자는 문자형 비교를 위한 자이므로 문자형을 기준으로 숫자형을 문자형으로 변환합니다.

 

SELECT * FROM 고객
WHERE 고객번호 LIKE '9410%'

--EXECUTION PLAN
--        SELECT STATEMENT
--            TABLE ACCESS (FULL) OF '고객'(TABLE)

-- PREDICATE INFORMATION
-- FILTER(TO_CHAR("고객번호") LIKE "9410%")

 

LIKE 연산자를 선택조건 처리 목적으로 사용하는 경우 형변환에 의해 발생하는 비효율

 

LIKE 연산자는 문자열을 비교하기 위한 연산자로 숫자형 칼럼에 사용하면 강제로 문자형으로 변환합니다. 이에 INDEX RANGE SCAN 할 수 없게 됩니다.

 

-- LIKE 연산자를 활용해 계좌번호를 선택입력조건으로 사용하는 쿼리
-- 사용자가 계좌번호를 입력하지 않으면 null || '%' 이 되서 전체 계좌번호를 조회한다.

SELECT * 
FROM 거래
WHERE 계좌번호 LIKE :acnt_no || '%'가공된 인덱스 컬럼을 사용하면 발생하는 비효율
AND 거래일자 between :trd_dt1 and :trd_dt2

 

만약 거래일자 + 계좌번호 인덱스라면 INDEX RANGE SCAN을 할 수 있지만 거래일자의 범위가 계좌번호조건보다 넓기 때문에 스켄효율이 현저히 낮아집니다.

 

자동형 변환으로 인해 발생하는 런타임 에러와 결과 오류

 

문자형 칼럼이 숫자형으로 자동 형변환 할 때 숫자형태로 변환할 수 없는 형식의 문자열이라면 실행도중 에러가 발생할 수 있습니다. 실행에러가 아니더라도 실행결과에 오류가 생기는 경우가 있습니다.

 

SELECT 
ROUND(AVG(SAL)) AVG_SAL
,MIN(SAL) MIN_SAL
,MAX(SAL) MAX_SAL
,MAX(DECODE(JOB, 'PRESIDENT', NULL, SAL)) MAX_SAL2
FROM EMP;

 

위 쿼리를 실행한 결과 급여를 가장 적게 받는 직원의 급여는 800이고 가장 많이 받는 직원의 급여는 5000입니다. 가장 급여를 많이 받는 직원의 JOB을 'PRESIDENT'로 예상하고 JOB 이 PRESIDENT 면 급여를 null로 하고 나머지는 SAL로 해서 최댓값을 구하면 실제 두 번째로 많이 받는 금액보다 훨씬 적은 금액이 출력됩니다. 이는 오라클 Decode 함수를 처리할 때 내부에서 사용하는 자동 형변환 규칙 때문에 발생한 결괏값 오류입니다.

 

DECODE(A, B, C, D)

A = B 이면 C를 반환하고 A!= B 이면 D를 반환하는 함수입니다. 반환값의 데이터 타입은 세 번째 인자인 C에 의해 결정됩니다.
만약 세 번째 인자가 NULL 이면 varchar2로 취급합니다.

 

DECODE 함수의 형변환 규칙 때문에 SAL 칼럼이 문자형으로 자동형 변환됩니다. 이에 숫자기준 최댓값이 아닌 문자열 기준 최댓값을 결과로 반환하게 됩니다.

 

 

자동형 변환에 대한 기준은 명확합니다. 옵티마이저의 자동형 변환에 의존하지 말고 인덱스 칼럼을 기준으로 반대편 칼럼이나 값을 명확히 형변환해야 합니다. TO_CHAR, TO_DATE, TO_NUMBER와 같은 형변환 함수를 적극적으로 사용해야 합니다. SQL의 성능은 함수의 호출 수나 연산 횟수가 아닌 블록 I/O의 횟수에 좌우되기 때문입니다.