1. WHERE절 가이드
1.1 WHERE절의 컬럼은 변형하지 않는다.
- WHERE절에서 사용하는 컬럼은 왠만해선 변형하지 않는다. 인덱스를 사용할 수 없기때문이다.
EX) SUBSTR(T1.ORD_YMD, 1, 6) = '201703' 보다는(INDEX FAST FULL SCAN)
T1.ORD_YMD LIKE '201703%'이 훨씬 낫다. (INDEX RANGE SCAN)
PREDICATE INFORMATION에서 FILTER와 ACCESS로 차이가난다. - WHERE절에서 사용하면 안되는 패턴 2가지
- 컬럼변형 : T1.ORD_ST || T1.PAY_TP = 'COMP' || 'BANK'
- 컬럼을 소문자로 변경해서 조건처리 : LOWER(T1.CUS_ID) = 'cus_0022';
- 첫번째는 컬럼별로 조건을 사용하고, 두번째는 a문자열을 upper처리해야한다.
1.2 날짜조건 처리하기
- 바른 사용법 : 문자열 자료형 컬럼 vs 문자열 자료형 조건 값.
컬럼의 데이터 타입에 맞는 조건을 사용해야한다! - 날짜컬럼이 문자형이면 문자열 변수로 비교
- 날짜컬럼이 date자료형이면 문자열 변수로 처리해도 문제없다
(자동으로 문자형이 date자료형으로 변환된다)
하지만 명확성과 타 dbms이관을 고려해 to_date처리를 권장 - 테이블의 날짜 컬럼은 절대 변환하지 않는다.
- 날짜 컬럼이 시분초가 입력된 date자료형이면 범위 조건을 사용해야 한다.
1.3 조건값은 컬럼과 같은 자료형을 사용한다.
- 오라클은 서로 다른 자료형에 비교가 발생하면 우선순위에 따라 한쪽 기준으로 자료형을 자동 변환
ex) 숫자문자간 비교가발생하면 문자를 숫자로 변환.t1.ord_ymd = 20170313 의 조건이
Predicate Information에서는
2-filter(TO_NUMBER("T1"."ORD_YMD") = 20170313) 으로 작동함
1.4 NOT IN보다는 IN을 사용한다(긍정형 조건을 사용하자.)
- IN을 사용하면 여러개의OR조건을 하나의 조건으로 처리할수있다.
- NOT IN뿐아니라 !=등의 부정조건은 피하는것이 좋다. > 인덱스를 효율적으로 사용하지 못할 가능성
1.5 불필요한 LIKE는 제거하자
- LIKE가 많으면 인덱스구성에 어려움이있음
- 복합인덱스의 선두컬럼으로 선택되지 못할 수도 있다. > 성능에 손해
- 등치(=)조건이 LIKE보다 인덱스를 사용할 가능성이 크다.
2. 불필요한 부분 제거하기
2.1 불필요한 COUNT는 하지 않는다.
- 집계함수를 불필요한곳에 사용하지 않는다.
- COUNT사용전 EXISTS로 처리할수있는지 고민
오늘 주문이 있는지 확인할려고할때 이렇게 전체조회하는것보다는
SELECT COUNT(*)
FROM TABLE
WHERE DT = '20170225'
AND CUS_ID ='CUS_0006'한건만 읽어서 처리하는것이 훨씬 효율적이다.
SELECT NVL(MAX(1),0)
FROM DUAL
WHERE EXISTS(SELECT *
FROM TABLE T1
WHERE DT='20170225'
AND T1.CUS_ID ='CUS_0006')dual~ EXISTS는 ROWNUM으로 대체가능
SELECT NVL(MAX(1),0) EX_DATA
FROM TABLE
WHERE CUS_ID ='CUS_0006'
AND ROWNUM<1;
2.2 COUNT에서 불필요한 부분은 제거한다.
- COUNT를 사용해야할때, 불필요한 조인테이블은 제거하고 꼭필요한 테이블만 참조한다.
- 카운트에 영향을 주지않는 조인, 서브쿼리, ORDER BY등은 제거하고 카운트하자
- 결과에 영향이있는 조인은 제거하지 않도록 주의!!
2.3 불필요한 컬럼은 사용하지 않는다.
- SELECT * 을 사용하지말자 : 일회성 쿼리인경우는 상관없으나, 서비스하는 SQL이라면 꼭 필요한 컬럼만 SELECT절에 적어주는 것이 좋다.
- 테이블 액세스(TABLE ACCESS BY INDEX ROWID)횟수를 줄일 수 있다.
동일 테이블의 반복 서브쿼리를 제거하자.
- 반복사용되는 사례 : 스칼라서브쿼리에서 동일 테이블을 반복접근하는경우 인라인뷰로빼는것을 고려해볼만하다(실행계획 참조)
이럴경우 그냥 M_CUS와 한번조인하는것이 낫다.
,(SELECT A.CUS_NM FROM M_CUS A WHERE A.CUS_ID = T1.CUS_ID) CUS_NM
,(SELECT A.EML_AD FROM M_CUS A WHERE A.CUS_ID = T1.CUS_ID) EML_AD
3. 생각의 전환
3.1 사용자 함수 사용의 최소화
- 오라클은 사용자 함수를 제공, 복잡한 로직을 함수로 구현가능
- 구현된 사용자함수는 SQL과조합해서 사용가능
- 매우 복잡한 수식이 반복사용되는경우가아니면 사용자 함수는 최소화하는것이좋다.
- 실행계획에는 사용자 함수에 대한 비용이 별도로 나타나지 않는다, BUFFER수치로 가늠
3.2 작업량을 줄이자
- SQL성능을 높이려면 I/O를 개선하는것이 기본원칙
- I/O를 줄일수없다면 작업량을 줄이는 아이디어가 필요
- 성능개선의 예시
- 사용컬럼 전체를 인덱스로 구성
- FULL SCAN이 빨리지도록 멀티블록I/O 높게 잡기
- SQL병렬처리
- 수식의 횟수를 줄일수있따면 인라인뷰에서 모수를 미리 줄여볼수있다.
- 데이터가많은 조인테이블은 미리 GROUP BY를 한다면 성능 향상을 할 수 있다.
3.3 집계테이블을 고민하자.
- 조회가 오래걸리는 쿼리는 집계테이블에 담아놓고 집계결과를 빠르게 조회할수있다.
- 단점 : 관리비용 증가, 비실시간데이터, 집계과정 오류
