친절한 SQL 튜닝 - 3장 인덱스 튜닝

3장 인덱스 튜닝

• SQL 튜닝은 랜덤 I/O와의 전쟁임을 거듭 강요한다.
• 3장에서는 테이블 랜덤 액세스를 최소화하는 구체적인 방법에 대해 소개한다.

3.1.1 테이블 랜덤 액세스

• 인덱스를 이용해도 조건절에 해당하는 데이터만 골라서 읽는데 파티션 Pruning은 왜 필요할까 굳이 파티션이 필요할까? 에 대한 질문에 대한 답을 소개한다.

인덱스로 검색해도 빠른데 왜 굳이 파티셔닝을 할까?
인덱스로 검색하는데 왜 느릴까?

• 데용량 데이터를 인덱스 사용하면 데이터가 금방 조회된다는 사실, 대량 데이터를 조회할 때 인덱스를 사용하니 테이블 전체를 스캔할 때보다 훨씬 느리다는 사실은 SQL 튜닝 핵심을 이해하면 이해할 수 있다.

인덱스 ROWID 는 물리적 주소? 논리적 주소?

SELECT * FROM 고객 WHERE 지역 = '서울';

• 위의 쿼리는 인덱스를 이용해 테이블을 액세스하는 SQL 실행계획이다.
• SQL이 참조하는 컬럼을 인덱스가 모두 포함하는 경우가 아니면, 인덱스를 스캔한 후에 반드시 테이블을 액세스한다.
• 인덱스를 스캔하는 이유는, 검색 조건을 만족하는 소량의 데이터를 인덱스에서 빨리 찾고 ROWID를 얻으려는데 있다.
• ROWID는 물리적 주소보다는 논리적 주소에 가깝다.

• 인덱스는 항상 도서 색인에 비유한다. 색인에 기록된 페이지 번호가 ROWID에 해당한다.
• 정리하자면 ROWID는 논리적 주소이며, 테이블 레코드를 찾아가기 위한 위치 정보를 담는다.

I/O 메커니즘 복습

• DBA(데이터 파일 번호 + 블록번호)는 디스크 상에서 블록을 찾기 위한 주소 정보이다.
• 그렇다고 매번 블록을 읽을 수는 없기에 I/O 성능을 높이려면 버퍼캐시를 활용해야 한다.
• 그래서 블록을 읽을 때는 디스크로 가기전에 항상 버퍼 캐시부터 찾아본다.
• 읽고자 하는 DBA를 해시 함수에 입력해서 해시 체인을 찾고 거기서 버퍼 헤더를 찾는다.

• 인덱스 ROWID를 이용한 테이블 액세스는 고비용 이기에 읽어야 할 데이터가 일정량을 넘는 순간, 테이블 전체를 스캔하는 것보다 오히려 느려진다.
• 인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려지게 만드는 가장 핵심적인 두 가지 요인은 아래와 같다.

1. Table Full Scan 은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 액세스는 랜덤 액세스 방식이다.
2. Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Single Block I/O 방식이다.

3.1.2 인덱스 컬럼 추가

• 테이블 액세스 최소화를 위해 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가 하는 것이다.
• EMP 테이블에 DEPTNO + JOB 순으로 구성한 EMP_X01 인덱스가 있는 상태에서 수행하려고 한다.

SELECT /*+ INDEX(EMP EMP_X01) */
    *
FROM 
    EMP
WHERE 
    DEPTNO = 30
    AND SAL >= 2000

• 만족하는 사원은 한 명인데 한 명을 찾기 위해 테이블을 여섯 번 액세스 하고 있다.
• 인덱스 구성을 DEPTNO + SAL 순으로 변경하면 좋겠지만 실 운영 환경에서는 사용하는 인덱스의 경우도 있을 수 있기 때문에 쉽지 않다.
• 할 수 없이 인덱스를 새로 만들어야겠지만 계속 인덱스를 추가하다보면 관리 비용은 물론 DML 부하에 따른 트랜잭션 성능 저하가 생길 수 있다.
• 이럴 때는 기존 인덱스에 SAL 컬럼을 추가하는 것만으로 큰 효과를 얻을 수가 있다. (인덱스 스캔량은 줄지 않지만 테이블 랜덤 액세스 횟수를 줄여주기 때문)

• 기존 인덱스에 SAL 컬럼 하나만 추가해도 테이블 액세스 횟수가 1회로 준다.

인덱스에 컬럼을 추가해서 튜닝했던 실제 사례

SELECT 
    렌탈관리번호, 고객명, 서비스번호 ...
FROM
    로밍렌탈 
WHERE 
    서비스번호 LIKE '010%'
    AND 사용여부 'Y'

• 서비스번호 단일 컬럼으로 구성된 인덱스를 사용했을 때 스캔 건수는 266,476이다.
• 이 단계에서만 266,968 - 1,011 개 블록으로 265,957개 블록을 읽었다.
• 이는 테이블을 총 266,476 번 반복하는 동안 블록 I/O가 265,957개 발생했다. (클러스터링 팩터가 매우 안좋은 상태)
• 데이터량이 워낙 많다보니 서비스번호 조건을 만족하는 데이터가 뿔뿔이 흩어져 있음을 알 수 있다.
• 여기서 더 큰 문제는 테이블을 총 266,476번 방문했지만, 최종 결과집합이 1,909건뿐이라는 데에 있다. 테이블을 방문하고서 사용여부 = 'Y'조건을 체크하는 과정에서 대부분 걸러진 것을 유추할 수 있다.
• 아래는 로밍렌탈_N2 인덱스에 사용여부 컬럼을 추가하고 나서의 SQL 트레이스 결과이다.

• 인덱스를 거쳐 테이블을 1,909번 방문했고, 모두 결과집합에 포함되었으며 불필요한 테이블 액세스가 전혀 발생하지 않았다.
  또한 불필요한 작업을 줄인 만큼 총 블록 I/O도 2,902개로 줄었다.

3.2 부분범위 처리 활용

• 테이블 랜덤 액세스가 성능에 미치고 인덱스에 컬럼을 추가하여 테이블 저장 구조를 개선하는 방법을 살펴보았다.
• 3.2장에서 설명하는 부분범위 처리 활용은 테이블 랜덤 액세스로 인한 인덱스 손익분기점의 한계를 극복할 히든카드를 추가로 소개한다.
• 예를 들어 공사장에서 미장공이 시멘트를 이용해 벽돌을 쌓는 동안 운반공이 벽돌을 실어 나르고 있다고 생각해보자.
• 운반공은 미장공이 벽돌을 더 가져오라는 요청이 있을 때만 벽돌을 실어 나르고 추가 요청이 없으면 운반작업은 거기서 멈춘다.(미장공이 DB클라이언트, 운반공은 데이터를 읽어 클라이언트에 전송하는 서버 프로세스)
• DBMS가 클라이언트에 데이터를 전송할 때도 일정량씩 나누어 전송하는데, 전체 결과 집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰 서서 기다린다.
• 1억 건짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유는, DBMS가 데이터를 모두 읽어 한 번에 전송하지 않고 먼저 읽는 일정량을 전송하고 멈추기 때문이다.(데이터를 전송하고 나면 서버 프로세스는 CPU를 OS에 반환하고 대기 큐에서 잠을 잔다.)
• 위와 같이 전체 쿼리 결과집합을 쉼 없이 연속적으로 전송하지 않고 사용자로부터 Fetch Call이 있을 때마다 일정량씩 나누어 전송하는 것을 부분범위 처리라고 한다.
• 이러한 부분범위 처리 활용 특징을 이용해 중간에 멈췄다가 사용자의 추가 요청이 있을 때마다 데이터를 가져오도록 하고 앞쪽 일부만 출력하고 멈출 수 있는가에 따라 성능개선 활용이 가능하다. 아래는 부분범취 처리를 활용한 예제이다.

SELECT 
    게시글ID, 제목, 작성자, 등록일시
FROM 
    게시판
WHERE 
    게시판구분코드 = 'A'
ORDER BY 
    등록일시 DESC

1. INDEX 를 게시판구분코드 + 등록일시 로 구성하지 않았을 경우
   • 소트 연산을 생략할 수 없어 게시판구분코드 = 'A' 조건을 만족하는 모든 레코드를 인덱스에서 읽어야 하므로 많은 테이블 랜덤 엑세스가 발생한다.
2. INDEX 를 게시판구분코드 + 등록일시 로 구성한 경우
   • Sort Order By 오퍼레이션이 자동으로 제거된다. 전체 로우를 읽지 않고도 결과집합 출력을 시작할 수 있다.

인덱스 ROWID를 이용한 테이블 랜덤 엑세스는 고비용 구조이다. 인덱스를 이용해 대량 데이터를 조회하면, 디스크 I/O 발생량도 함꼐 증가하므로 성능이 급격히 나빠진다.

하지만 부분범위 처리 원리를 활용해 상위 N개의 집합을 빠르게 출력한다면 아무리 대상 레코드가 많아도 빠른 응답속도를 낼 수 있다. 

3.3 인덱스 스캔 효율화

• IOT, 클러스터, 파티션은 테이블 랜덤 액세스를 최소화하는데 효과적인 저장 구조이지만, 운영 시스템 환경에서는 많은 테스트를 진행해야 하므로 어려움이 따른다.
• 운영 환경에서 가능한 일반적인 튜닝 기법은 인덱스 컬럼 추가이다.
• 인덱스 컬럼 추가에 이어서 인덱스 스캔 효율화에 대해서 알아보자.

3.3.1 인덱스 탐색

• 인덱스 스캔 효율화 튜닝을 이해해서는 인덱스 수직적 탐색과 수평적 탐색을 좀 더 깊이 있게 다룰 필요가 있다.

인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 생기는 비효율

• 인덱스 스캔 효율성은 인덱스 컬럼을 조건절에 모두 등치(=) 조건으로 사용할 때 가장 좋다.
• 리프 블록을 스캔하면서 읽은 레코드는 하나도 걸러지지 않고 모두 테이블 액세스로 이어지므로 인덱스 스캔 단계에서 비효율은 전혀 없다.
• 인덱스 컬럼 중 일부가 조건절에 없거나 등치 조건이 아니더라도, 그것이 뒤쪽 컬럼일 떄는 비효율이 없다.
• 인덱스를 [ 아파트시세코드 + 평형 + 평형타입 + 인터넷매물 ] 순으로 구성했을 때 아래와 같은 경우를 말한다.
• WHERE 아파트시세코드 = :a WHERE 아파트시세코드 = :a AND 평형 = :b WHERE 아파트시세코드 = :a AND 평형 = :b AND 평형타입 = :c WHERE 아파트시세코드 = :a AND 평형 = :b AND 평형타입 = :c BETWEEN :c AND :d
• 반면 인덱스 선행 컬럼이 조건절에 없거나 부등호, BETWEEN, LIKE 같은 범위검색 조건이면 , 인덱스를 스캔하는 단계에서 비효율이 생긴다.
• 예를 들어, 인덱스를 [ 아파트시세코드 + 평형 + 평형타입 + 인터넷매물 ] 순으로 구성한 상황에서 아래의 SQL 을 수행하는 경우를 살펴보자.

SELECT  
    해당층, 평단가, 입력일, 해당동 ...
FROM
    매물아파트매매
WHERE 
    아파트시세코드 = 'A01245124140'
    AND 평형 = '59'
    AND 평형타입 = 'A'
    AND 인터넷매물 BETWEEN '1' AND '3'
ORDER BY 입력일 DESC

• 인터넷 매물이 BETWEEN 조건이지만 선행 컬럼들 모두 '=' 조건이기 때문에 전혀 비효율 없이 조건을 만족하는 세 건을 빠르게 찾았다. 여기서 비효율이 없다는 것은 세 건을 찾기 위해 단 네건만 스캔했음을 의미한다.
• 인덱스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은, 조건을 만족하는 레코드가 모두 한데 모여 있기 때문이다.
• 인덱스 구성은 [ 인터넷매물 + 아파트시세코드 + 평형 + 평형타입 ] 순으로 바꾼 후 SQL을 수행하면 인덱스 스캔 범위가 넓어진다.

3.3.2 BETWEEN을 IN_LIST 전환

• 범위검색 컬럼이 맨 뒤로 가도록 인덱스를 [ 아파트시세코드 + 평형 + 평형타입 + 인터넷매물 ] 순으로 변경하면 좋겠지만 운영 시스템에서 인덱스 구성을 바꾸기는 쉽지 않다. 이럴 때 BETWEEN 조건을 아래와 같이 IN_LIST 로 바꿔주면 큰 효과를 얻는다.
• 아래의 SQL과 그림, 실행계획을 보자.

SELECT  
    해당층, 평단가, 입력일, 해당동 ...
FROM
    매물아파트매매
WHERE 
    인터넷 매물 IN ('1', '2', '3')
    AND 아파트시세코드 = 'A01245124140'
    AND 평형 = '59'
    AND 평형타입 = 'A'
ORDER BY 입력일 DESC

• 왼쪽 화살표가 세 개인 이유는 인덱스 수직적 탐색이 세 번 발생하기 때문이다.
• 이 때의 실행계획은 아래 INLIST ITERATOR 와 같다.
• 이는 인덱스를 세 번 탐색한다는 사실이며 세 번 탐색한다는 것은 SQL을 아래와 같이 작성한 것과 같으며 모든 컬럼이 '=' 조건인 것에 주목하자.

• IN-LIST 개수만큼 UNION ALL 브랜치가 생성되고 각 브랜치마다 모든 컬럼을 '=' 조건으로 검색하므로 앞서 선두 컬럼에 BETWEEN을 사용할 때와 같은 비효율이 사라진다.
• 하지만 IN-List 항목 개수가 늘어날 수 있다면 BETWEEN 을 IN-List 전환하는 방식은 사용하기 곤란하므로 아래와 같이 NL 방식의 조인문이나 서브쿼리로 구현하면된다. (IN-List 값들을 코드 테이블로 관리하고 있을 때 가능)

SELECT /*+ ORDERED USE_NL(B)*/
    B.해당층, B.평당가, B.입력일, B.해당동...
FROM 
    통합코드 A, 매물아파트매매 B
WHERE 
    1=1
    AND A.코드구분 = 'CD064'
    AND A.코드 BETWEEN '1' AND '3'
    AND B.인터넷매물 = A.코드
    AND B.아파트시세코드 = 'A010113500056'
    AND B.평형 = '59'
    AND B.평형타입 = 'A'
ORDER BY 
    B.입력일 DESC

• NL조인을 아직 정확히 모르지만 인터넷매물을 '=' 조건으로 조인하고 있다는데 주목하자.

BETWEEN 조건은 IN-LIST로 전환할 때 주의 사항

• 단 BETWEEN 조건을 IN-LIST 조건으로 전환할 때는 IN-LIST 개수가 많지 않아야 한다는 것이다. IN-LIST 개수가 많아지면 대량의 수직적 탐색이 발생하므로 BETWEEN 조건의 리프 블록을 많이 탐색하는 비효율보다 IN-LIST 조건의 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 있다.
• 인덱스 스캔 과정에 선택되는 레코드들이 서로 멀리 떨어져 있을 때만 유용하다는 사실은 기억해야 한다.
• 아래 조건절을 예로 들면 [고객등급 + 고객번호] 순으로 구성한 인덱스에서 고객번호 = 123 조건을 만족하는 레코드가 서로 멀리 떨어져 있을 때만 BETWEEN 조건을 IN-LIST 로 전환하는 기법이 유용하다.

WHERE 고객등급 BETWEEN 'C' AND 'D'
AND 고객번호 = 123

• 위의 쿼리의 조건을 만족하는 레코드가 2건밖에 없을 때 이 두건에 대해 인덱스 리프 블록을 많이 스캔해야한다. (이럴 때 BETWEEN 을 IN-LIST 로 변환하면 효과가 크다.)

• 위의 그림은 둘 사이에 놓인 인덱스 블록이 소량이기에 BETWEEN을 IN-LIST로 변환하면 효과가 전혀 없거나 수직적 탐색 때문에 오히려 블록 I/O가 더 많이 발생한다.
• 정리하자면, BETWEEN 조건 때문에 인덱스를 비효율적으로 스캔하더라도 블록 I/O 측면에서는 소량으로 그치는 경우가 많고 IN-LIST 개수가 많으면 수직적 탐색 과정에서 많은 블록을 읽게 된다. 따라서 데이터 분포나 수직적 탐색 비용을 고려하여 BETWEEN 과 IN-LIST를 사용하자.

3.3.3 Index Skip Scan 활용

• 위에 그림 처럼 BETWEEN 조건을 IN-List 조건으로 변환하지 않아도 Index Skip Scan을 활용하면 된다.

SELECT 
    COUNT(*)
FROM 
    월별고객별판매집계 T
WHERE 
    판매구분 = 'A'
    AND 판매월 BETWEEN '201801' AND '201812'

• 위에 쿼리의 INDEX는 아래와 같이 구성한다.

CREATE INDEX 월별고객별판매집계_IDX1 ON 월별고객별판매집계 (판매구분, 판매월);

• 위와 같이 인덱스를 사용하면 281개의 블록 I/O가 발생함을 알 수 있고 테이블 액세스는 전혀 발생하지 않는다.
• 이번에는 BETWEEN 조건의 판매월 컬럼이 선두인 인덱스를 이용해보자.

CREATE INDEX 월별고객별판매집계_IDX2 ON 월별고객별판매집계 (판매월, 판매구분);

• 판매구분 = 'A' 레코드는 2018년 1월부터 12월까지 각 판매월 앞쪽에 위치하게되어 3,090개 블록 I/O 가 발생한다.
• 이유는 인덱스 선두 컬럼이 BETWEEN 조건이어서 판매구분이 'B'인 레코드까지 모두 스캔하고서 버렸기 때문이다.
• BETWEEN 방식은 IN-LIST 방식을 적용하고 다시 실행시켜보자.

SELECT 
    COUNT(*)
FROM 
    월별고객별판매집계 T
WHERE 
    판매구분 = 'A'
    AND 판매월 IN ('201801' ... '201812')

• 3,090개 였던 블록 I/O 개수가 314개로 감소했다. 인덱스 브랜치 블록을 열두 번 반복 탐색했지만, 리프 블록을 스캔할 때의 비효율을 제거함으로써 성능이열 배 좋아졌다. 마지막으로 INDEX SKIP SCAN을 유도해보자.

SELECT /*+ INDEX_SS(T 월별고객별판매집계_IDX2)*/
    COUNT(*)
FROM 
    월별고객별판매집계 T
WHERE 
    판매구분 = 'A'
    AND 판매월 BETWEEN '201801' AND '201812'

• 인덱스 선두 컬럼이 BETWEEN 조건인데도 단 300블록만 읽고 일을 마쳤다.
• 결론적으로는 INDEX SKIP SCAN이 IN-LIST 보다 오히려 낫고 IDX1 을 사용했을 때와 비슷한 성능을 보여준다.

3.3.4 IN 조건은 '='인가

• SQL 튜닝 입문자들은 흔히들 IN 조건을 '=' 조건과 동등시한다는 점이다.
• SQL에 대한 인덱스를 [상품ID + 고객번호] , [고객번호 + 상품ID] 로 설계했을 때의 차이점을 통해 쉽게 알 수 있다.
• 흔히 차이가 없다고 생각하지만 면밀히 말하자면 IN 조건은 '='이 아니다.
• 즉, 인덱스의 구성에 따라 성능이 달라진다.

SELECT  *
FROM    고객별가입상품
WHERE   고객번호 = :CUST_NO
AND     상품ID IN ('NH00037', 'NH00041', 'NH00050')

인덱스 [상품ID + 고객번호] 인 경우

• 인덱스가 [상품ID + 고객번호] 로 구성되어있을 경우 IN-LIST 조건으로 푸는 것이 효과적이다.
• 상품 ID가 인덱스 선두 컬럼인 상황에서 IN-LIST 방식으로 풀지 않으면, 상품ID는 필터 조건이므로 테이블 전체 또는 인덱스 전체를 스캔하면서 필터링해야 한다.

인덱스 [고객번호 + 상품ID] 인 경우

• 상품ID 조건절을 IN-LIST 방식으로 풀지 않으면, 상품ID 조건절은 필터로 처리한다.
• 이렇게되면 고객번호만 액세스 조건이므로 고객번호에 해당하는 레코드를 모두 스캔한다.
• 같은 고객은 한 블록이거나 연속된 두 블록에 모여있으므로 블록 I/O는 수직적 탐색 과정을 포함해 총 세 개 또는 네 개만 발생한다.

3.3.5 BETWEEN과 LIKE 스캔 범위 비교

• LIKE와 BETWEEN은 둘 다 범위검색 조건으로서, 앞서 설명한 비효율 원리가 똑같이 적용되지만, 데이터 분포와 조건절 값에 따라 인덱스 스캔량이 서로 다를 수 있다.
• 결론부터 말하면, LIKE보다 BETWEEN을 사용하는 게 낫다. BETWEEN을 사용하면 적어도 손해는 안본다.
• 예를 들어 인덱스를 [판매월 + 판매구분] 순으로 구성했다고 가정하자.
• 판매구분으로는 'A'와 'B' 두 개 값이 존재하고, 각각 90%, 10% 비중을 차지하는 상황에서 두 조건절에 대해 인덱스 스캔량을 비교해 보자.

WHERE 판매월 BETWEEN '201901' AND '201912'
AND   판매구분 = 'B'

WHERE 판매월 LIKE '2019%'
AND   판매구분 = 'B'

• 그림 3-41 조건절1처럼 BETWEEN은 판매월 = '201901' 이고 판매구분 = 'B' 인 첫 번째 레코드에서 스캔을 시작한다.
• 반면 조건절2는 판매월 = '201901' 인 첫 번째 레코드에서 스캔을 시작하고, '201900' 이 저장돼 있다면 그 값도 읽어야 하므로 판매구분 = 'B'인 지점으로 바로 내려갈 수 없다.

WHERE 판매월 BETWEEN '201901' AND '201912'
AND   판매구분 = 'A'

WHERE 판매월 LIKE '2019%'
AND   판매구분 = 'A'

• 조건절 3은 판매월 = '201912'이고 판매구분 'B' 인 첫 번째 레코드를 만나는 순간 스캔을 멈춘다.
• 반면 조건절 4는 판매월 = '201912'인 레코드를 모두 스캔하고 나서야 멈춘다, 혹시라도 '201913'이 저장돼 있다면 그 값도 읽어야 하므로 중간에 멈출 수가 없기 때문이다.

범위검색 조건을 남용할 때 생기는 비효율

• 사용자 입력과 선택에 따라 편리하게 사용하기 위해 종종 개발자는 조건절에 BETWEEN 과 LIKE 를 무분별하게 사용한다.
• 하지만 옵션 조건을 처리하기 위해 대량 테이블을 넓은 범위로 검색할 때는 영향이 클 수도 있기 때문에 데이터 분포에 따라 인덱스 컬럼에 대한 비교 연산자를 신중하게 선택해야 한다.

3.3.6 다양한 옵션 조건 처리 방식의 장단점 비교

• 방금 살펴본 BETWEEN 과 LIKE 이외에도 다양한 방식으로 옵션 조건을 처리하는 방식이 존재한다.

OR조건 활용

SELECT * FROM 거래
WHERE :CUST_ID IS NULL OR 고객ID = :CUST_ID
AND   거래일자 BETWEEN :DT1 AND :DT2

• 흔히 사용하는 이 방식의 문제점은 쿼리 변환이 기본적으로 작동하지 않아 인덱스가 존재해도 사용할 수 없다는 문제점이다.
• 따라서 인덱스 선두 컬럼에 대한 옵션 조건에는 OR조건을 사용해선 안 된다.
• 인덱스에 포함되지 않은 컬럼에 대한 옵션 조건은 어차피 테이블에서 필터링할 수 밖에 없으므로 그럴 때는 OR조건을 사용해도 무방하다.
• OR 조건을 활용한 옵션 조건 처리를 정리하면 아래와 같다.
  • 인덱스 액세스 조건으로 사용 불가
  • 인덱스 필터 조건으로도 사용 불가
  • 테이블 필터 조건으로만 사용 가능

LIKE/BETWEEN 조건 활용

• LIKE와 BETWEEN 도 옵션 조건 처리를 위해 많이 사용하는 방식 중 하나이다.
• 필수 조건 컬럼을 인덱스 선두에 두고 액세스 조건으로 사용하면, LIKE/BETWEEN 이 인덱스 필터 조건이어도 충분히 좋은 성능을 낼 수 있기 때문이다.

-- 인덱스 : 등록일시 + 상품분류코드
SELECT * FROM 상품
WHERE 등록일시 >= TRUNC(SYSDATE)            -- 필수 조건
AND   상품분류코드 LIKE :PRD_CLS_CD || '%'  -- 옵션 조건

-- 인덱스 : 상품명 + 상품분류코드
SELECT * FROM 상품
WHERE 상품명 = :PRD_NM
AND   상품분류코드 LIKE :PRD_CLS_CD || '%'

• 위와 같은 필수 조건은 좋은 성능을 나타낼 수 있으나 문제는 필수 조건의 번별력이 좋지 않을 경우이다.

-- 인덱스 : 상품대분류코드 + 상품코드 
SELECT * FROM 상품
WHERE 상품대분류코드 = :PRD_LCLS_CD
AND   상품코드 LIKE :PRD_CD || '%'

• 위 SQL에서 상품대분류코드만으로 조회할 때는 TABLE FULL SCAN이 유리하다.
• 하지만 옵티마이저는 상품코드까지 입력할 때를 기준으로 INDEX RANGE SCAN을 선택한다.
• 사용자가 상품코드까지 입력하면 최적의 성능을 내지만, 그렇지 않을 때는 성능에 문제가 생긴다.
• LIKE/BETWEEN 조건을 활용할 때는 아래 네 가지 경우에 속하는지 반드시 점검해야 한다.
  • 인덱스 선두 컬럼
  • NULL 허용 컬럼
  • 숫자형 컬럼
  • 가변 길이 컬럼

3.3.7 함수호출부하 해소를 위한 인덱스 구성

PL/SQL 함수의 성능적 특성

• PL/SQL 사용자 정의 함수는 개발자들이 일반적으로 생각하는 것보다 매우 느리다.
• PL/SQL 사용자 정의 함수가 느린 데에는 아래 3가지 이유가 존재한다.
  1. 가상머신에서 실행되는 인터프리터 언어
  2. 호출 시마다 컨텍스트 스위칭 발생
  3. 내장 SQL에 대한 Recursive Call 발생
• 오라클은 PL/SQL로 작성한 함수와 프로시저를 컴파일하면 JAVA 언어처럼 바이트코드를 생성해서 데이터 딕셔너리에 저장하며, PL/SQL 엔진만 있으면 어디서든 실행할 수 있으며 PL/SQL 엔진은 바이트 코드를 런타임 시 해석하면서 실행한다.
• 대게 PL/SQL 함수에는 SQL이 내장돼어있어 일반적으로 인터프리팅, 컨텍스트 스위칭보다 Recursive Call 부하가 가장 크다.

SELECT 회원번호, 회원병, 생년, GET_ADDR(우편번호)
FROM 회원
WHERE 생월일 LIKE '01%'

SELECT A.회원번호, A.회원명, A.생년
        (SELECT B.시도 || ' ' || B.구군 
         FROM 기본주소 B
         WHERE B.우편번호 = A.우편번호
         AND B.순번 = 1) 기본주소
FROM 회원 A
WHERE A.생월일 LIKE '01%'

• 두 가지 쿼리를 비교해봤을 때 아래와 같이 조인문으로 처리하면 성능 차이가 매우크며 당연히 아래 SQL이 더 빠르다.
• 하지만 PL/SQL 함수 내부 로직이 너무 복잡하면 그대로 쓸 수 밖에 없는데, 그럴 때 함수 호출 횟수를 줄이는 여러 방법이 바로 액세스 조건을 고려한 인덱스 구성이다.

효과적인 인덱스 구성을 통한 함수호출 최소화

SELECT /*+ FULL(A) */ 회원번호, 회원명, 생년
FROM 회원 A
WHERE 암호화된_전화번호 = ENCRYPTION( :PHONE_NO)

• FULL SCAN 방식으로 읽으면 ENCRYPTION 함수는 테이블 건수만큼 수행된다.
• 하지만 아래와 같이 다른 조건절이 존재하면 조건적을 만족하는 건수만큼 수행된다.

SELECT /*+ FULL(A) */ 회원번호, 회원명, 생년
FROM 회원 A
WHERE 생년 = '1987' 
AND 암호화된_전화번호 = ENCRYPTION( :PHONE_NO)