2.2.5 ORDER BY 절에서 컬럼 가공
인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다. 에서 말하는 인덱스 컬럼은 대개 조건절에 사용한 컬럼을 말한다.
그런데 조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공함으로 인해 인덱스를 정상적으로 사용할 수 없는 경우도 종종 있다.
아래의 SQL문은 ORDER BY 절이 있어도 정렬 연산을 생략할 수 있는 상태인데, 실행계획에 SORT ORDER BY 연산이 나타난다.
SELECT * FROM(
SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :dt
AND A.주문번호 > NVL(:next_ord_no, 0)
ORDER BY 주문번호
)
WHERE ROWNUM <=30;SORT ORDER BY 연산이 나타나는 원인은 ORDER BY절에 기술한 '주문번호'는 순수한 주문번호가 아니라 TO_CHAR 함수로 가공한 주문번호를 가리키기 때문이다.
SORT ORDER BY 연산이 나타나지 않도록 해결하려면 ORDER BY절 주문번호에 A(주문 테이블 Alias)만 붙여주면 된다.
SELECT * FROM(
SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호, A.주문금액
FROM 주문 A
WHERE A.주문일자 = :dt
AND A.주문번호 > NVL(:next_ord_no, 0)
ORDER BY A.주문번호
)
WHERE ROWNUM <=30;아래의 SQL문도 정렬 연산을 생략할 수 없다.
SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'인덱스에는 문자열 기준으로 정렬돼 있는데, 이를 숫자값으로 바꾼 값 기준으로 최종 변경순번을 요구했기 때문에 정렬 연산 생략이 불가능하다.
물론 아래와 같이 SQL을 수정하면 정렬 연산 없이 최종 변경순번을 찾을 수 있다.
애초에 데이터타입을 숫자형으로 설계했다면 이렇게 튜닝할 일도 없었을 것이다.
SELECT NVL(TO_NUMBER(MAX(변경순번)),0)
FROM 상태변경이력
WHERE 장비번호 = 'C'
AND 변경일자 = '20180316'2.2.7 자동 형변환
아래 SQL은 생년월일 컬럼을 조건절에서 가공하지 않았는데도 옵티마이저는
테이블 전체 스캔을 선택했다.
SELECT * FROM 고객
WHERE 생년월일 = 19821225옵티마이저가 해당 SQL을 아래와 같이 변환하고, 인덱스 컬럼이 가공됐기 때문에 인덱스를 Range Scan 할 수 없게 된 것이다.
SELECT * FROM 고객
WHERE TO_NUMBER(생년월일) = 19821225DBMS중에는 타입 체크를 엄격히 함으로써 컴파일 시점에 에러를 내는 DBMS와 오라클과 같이 자동으로 형변환 처리를 해주는 DBMS가 있다.
오라클에서 숫자형과 문자형이 만나면 숫자형 컬럼 기준으로 문자형 컬럼을 변환한다.
날짜형과 문자형이 만나면 날짜형이 이긴다.
숫자형과 문자형이 만나면 숫자형 컬럼 기준으로 문자형 컬럼을 변환하지만,
LIKE 연산자의 경우 문자열 비교 연산자이므로 이때는 문자형 기준으로 숫자형 컬럼이 변한된다.
자동 형변환 주의
자동 형변환이 작동하면 편리하다고 생각할 수 있지만, 이 기능 때문에 성능과 애플리케이션 품질에 문제가 생길 수 있다.
아래와 같이 숫자형 컬럼(n_col)과 문자형 컬럼(v_col)을 비교하면 문자형 컬럼이 숫자형으로 변환되는데, 만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 입력되면 쿼리 수행 도중 에러가 발생한다.
where n_col = v_col
2행에 오류 :
ORA-01722: 수치가 부적합합니다.자동형변환 기능에 의존하지 말고, 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변환해 주어야 한다.
형변환 함수를 생략한다고 해서 연산 횟수가 주는 것도 아니다.
개발자가 형변환 함수를 생략해도 옵티마이저가 자동으로 생성하기 때문이다.
출처 : 친절한 SQL 튜닝(디비안, 조시형 저)
