인덱스 기본

인덱스 구조 및 탐색

SQL 튜닝은 랜덤 I/O와의 전쟁

• 인덱스 튜닝의 두 가지 핵심요소

  1. 인덱스 스캔 과정에서 발생하는 비효율을 줄이기 (인덱스 스캔 효율화 튜닝)
  2. 테이블 액세스 횟수를 줄이는 것 (랜덤 액세스 최소화 튜닝)

• NL 조인이 대량 데이터 조일할 때 느린 이유도 랜덤 I/O때문이고, 이를 보완한 소트머지 조인과 해시 조인도 결국 랜덤 I/O를 극복하는 것을 목표로 개발된 것이다.

인덱스 구조

• B*Tree 인덱스를 사용하며 루트 블록 - 브랜치 블록 - 리프 블록 구조

• 인덱스를 사용하면 Range Scan이 가능하며 인덱스는 정렬되어 있기 때문이다.
• 루트와 브랜치 블록에는 키값을 갖지 않는 특별한 레코드인 LMC(LeftMost Child)가 있고 LMC가 가르키는 주소로 찾아간 블록에는 키값 보다 작거나 같은 레코드가 저장되어 있다.
• 리프 블록에 저장된 각 레코드는 키값 순으로 정렬되어 있고 테이블 레코드를 가르키는 주소값 ROWID를 갖는다.
  • ROWID는 데이터 블록 주소와 로우 번호로 구성되어 있다.

    ROWID = 데이터 블록 주소 + 로우 번호
    데이터 블록 주소 = 데이터 파일 번호 + 블록 번호
    블록 번호 = 데이터 파일 내에서 부여한 상대적 순번
    로우 번호 = 블록 내 순번

인덱스 탐색 과정

인덱스 탐색 과정은 수직적 탐색과 수평적 탐색이 존재

• 수직적 탐색: 인덱스 스캔 시작지점 찾는 과정
  • 정렬된 인덱스 레코드 중 조건을 만족하는 첫 번째 레코드를 찾는과정
  • 수직적 탐색은 '조건을 만족하는 레코드를 찾는 과정이 아니라 조건을 만족하는 첫 번째 레코드를 찾는'과정
• 수평적 탐색: 데이터를 찾는 과정
  • 수직적 탐색을 통해 스캔 시작점을 찾은 후 원하는 데이터가 더 안 나타낼 때까지 인덱스 리프 블록을 수평적으로 스캔한다.
    • 인덱스 리프 블록끼리는 서로 앞뒤 블록에 대한 주소값을 갖는 Double Linked List 구조이다.
  • 인덱스를 수평적으로 탐색하는 이유
    • 조건절을 만족하는 데이터를 모두 찾기 위해서
    • ROWID를 얻기 위해서
• 인덱스 선두 컬럼을 모두 = 조건으로 검색할 때는 어느 컬럼을 인덱스 앞쪽에 두든 블록 I/O 개수가 같아서 성능이 똑같다.
  • '컬럼중에서 선택도가 낮은 컬럼을 앞쪽에 두고 결합인덱스를 생성해야 검사 횟수를 줄일수 있어 성능에 유리하다'라는 인덱스를 제대로 이해하지 못한 것이다.
    • DBMS의 인덱스는 B*Tree 방식을 사용하고 있기 때문에 컬럼의 순서와 상관없이 일량은 일정하다. -> 수직적 탐색의 장점

인덱스 기본 사용법

인덱스 기본 사용법이란 인덱스를 Range Scan 하는 방법을 의미한다.

• 인덱스를 정상적으로 사용한다는 의미는 리프 블록에서 스캔 시작점을 찾아 거기서부터 스캔하다가 중간에 멈추는 것을 의미한다. 즉, 리프 블록 일부만 스캔하는 Index Range Scan을 의미한다.

  • 인덱스를 정상적으로 사용하기 위해서는 인덱스 컬럼을 가공하지 않아야한다.
  • 인덱스 컬럼을 가공하게 되면 일부가 아닌 전체를 스캔하는 Index Full Scan 방식으로 작동한다.

• 인덱스 컬럼을 가공했을 때 인덱스를 정상적으로 사용할 수 없는 이유는 인덱스 스캔 시작점을 찾을 수 없기 때문이다.

  • LIKE로 중간 값을 검색 할 때도 Index Range Scan을 사용할 수 없다. 'Word'로 시작하는 값은 특정 구간에 모여 있지만, 포함하는 값은 전체 구간에 있기 때문이다.

  • OR 조건 = IN () 으로 검색할 때도 Range Scan 방식을 사용할 수 없다. 때문에 UNION ALL을 사용해서 개별적으로 조회하면 Range Scan이 가능하다.

    • IN 조건절에 대해서는 SQL 옵티마이저가 IN-List Iterator 방식을 사용한다. IN-List 개수 만큼 Index Range Sacn을 반복한다.
      

    • Q&A
      UNION ALL 방식으로 SELECT를 사용하는 방식을 사용하면 DISK I/O가 많아져서 오히려 안좋은게 아닌가??

인덱스 사용 조건

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 가공하지 않는 상태로 조건절에 있어야 한다는 사실이다.

• 다른말로 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 Index Range Scan은 무조건 가능하다.
  • 하지만 인덱스를 잘 탄다고 튜닝이 잘된 것일까?? -> 아니다.
  • 아래의 코드는 선두 컬럼을 가공하지않고 조건절에 있지만 인덱스가 불필요한 상황이다.

    잘된 예시
    SELECT * FROM 주문 상품 WHERE 주문일자 = :ord_dt AND 상품번호 = :ord_num
    
    잘못된 예시
    SELECT * FROM 주문 상품 WHERE 주문일자 = :ord_dt AND 상품번호 LIKE '%PING%'
    SELECT * FROM 주문 상품 WHERE 주문일자 = :ord_dt AND SUBSTR(상품번호, 1, 4) = 'PING';

인덱스를 이용한 소트 연산 생략

인덱스를 Range Scan 할 수 있는 이유는 데이터가 정렬돼 있기 때문이다.

• PK = 장비번호 + 변경일자 + 변경순번 순으로 구성되어 있다면

  SELECT * FROM 상태변경이력 WHERE 장비번호 = 'C' AND 변경일자 = '20220316'

  으로 조회를 한다면 변경순번으로 정렬된다. -> ORDER BY 변경순번없이!!
• ASC정렬일 때는 조건을 만족하는 가장 작은 값을 찾아 좌측으로 수직적으로 탐색 후 우측으로 수평 탐색을 한다.
• DESC정렬일 때는 조건을 만족하는 가장 큰 값을 찾아 우측으로 수직적 탐색한 후 좌측으로 탐색한다.

조건절이 아닌 ORDER BY 또는 SELECT-LIST에서 컬럼을 가공해서 인덱스를 사용할 수 없는 경우도 존재한다.

• ORDER BY 변경일자 || 변경순번으로 컬럼을 가공한다면 소트 연산 생략을 할 수 없다.

  • 

• SELECT문에서 가공한 컬럼을 ALIAS로 지정 후 ORDER BY에서 ALIAS로 사용하는 경우

  • ORDER BY 주문 번호 -> A.주문번호로 변경해야한다.

SELECT *
FROM (
	SELECT TO_CHAR(A.주문번호, 'FM000000') AS 주문번호, A.업체번호
    FROM 주문 A
    WEHRE A. 주문일자 = :dt
    AND   A. 주문번호 > NVL(:next_ord_no, 0)
    ORDER BY 주문번호
)
WHERE ROWNUM <= 30

SELECT-LIST에서 컬럼 가공

• MIN을 구할 때는 수직적 탐색으로 왼쪽으로 내려가 가장 왼쪽에 있는 첫번째 레코드를 MAX를 구할 때는 수직적 탐색으로 오른쪽으로 내려가 가장 오른쪽에 있는 첫번째 레코드를 구해서 정렬 연산을 수행하지않는다.
• 인덱스가 문자열 기준으로 정렬되어 있는데, 이를 숫자값으로 바꾼 값으로 변경하면 정렬연산을 생략할 수 없다.

  SELECT NVL(MAX(TO_NUMBER(변경순번)), 0)
  FROM   상태변경이력
  WHERE  장비번호 = 'C'
  AND    변경일자 = '20180316'

• INDEX가 [장비번호 + 변경일자 + 변경순번] 순으로 구성되어 있을 때 최종 변경일자에 더해 최종 변경 순번까지 출력하려면 어떻게 해야할까?

1. SELECT문안에 SUB QUERY로 MAX(변경일자), MAX(변경순번)을 조회하게 하면 인덱스를 사용할 수 있지만 테이블을 여러번 읽어햐 하므로 비효율적이다.
2. SELECT문안에 SUB QUERY로 MAX(변경일자 || 변경순번)으로 조회하면 인덱스 컬럼을 가공했기 때문에 모든 데이터를 읽어야하기 때문에 비효율적이다.
   -> TOP N 알고리즘을 적용(5장 공부 후 추후 보완)

자동 형변환

• 오라클은 자동 형변환 처리를 하는 DBMS이다.
  • 숫자형과 문자형이 만나면 숫자형이 이긴다.
    • LIKE는 문자열 비교 연산자 이므로 문자형 기준으로 숫자형 컬럼이 변환된다.
  • 날짜형과 문자형이 만나면 날짜형이 이긴다.
    • WHERE 가입일자 = TO_DATE('01-JAN-2018','DD-MON-YYYY') 처럼 날짜 포맷을 정확히 지정해주는 코딩 습관 중요
• 아래 쿼리는 사용자가 계좌 번호를 입력하지 않았을 때와 입력했을 때를 처리하기위한 쿼리이다.

  SELECT * FROM 거래
  WHERE 계좌번호 LIKE :acnt_no || '%'
  AND   거래일자 between :trd_dt1 and :trd_dt2

  • 계좌번호 컬럼이 숫자형일 때 특히 주의가 필요하다. 방금 설명했든, 숫자형 컬럼을 LIKE 조건으로 검색하면 자동 형변환이 발생해 계좌번호가 아예 인덱스 액세스 조건으로 사용되지 못하기 때문이다.
• 오라클이 decode 함수를 처리할 때 내부에서 사용하는 자동 형변환 규칙 존재
  • 반환 값의 데이터 타입은 세 번째 인자 c에 의해 결정된다.
  • 세 번째 인자 값이 null이라면 varchar2로 취급된다.
    • 문제점: 세 번째 반환값이 문자형이고 네 번재 반환값이 숫자형이면 네번째를 반환할 때 문자형 기준으로 반환하기 때문에 잘못된 결과값을 반환 할 수 있다.
      -> decode(job, 'PRESIDENT', to_number(null), sal)로 3번째 값을 숫자형으로 변경 해주어야한다.
• SQL 튜닝은 TO_CHAR, TO_DATE, TO_NUMBER같은 형변환을 안쓰는 것이 좋은게 아니라 결국 블록 I/O를 줄이는 것이 관건이다.

인덱스 확장기능 사용법

• 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄이는지가 관건이다.

INDEX RANGE SCAN

• 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다.

INDEX FULL SCAN

• 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식이다.
• 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택된다.
• 인덱스 스캔 단계에서 대부분 레코드를 필터링하고 아주 일부만 테이블을 액세스하는 상황에서 큰 테이블 스캔 보다 효율적이다.
  -> 아래는 SAL > 9000인 사원이 아주 일부일 경우 효율적인 쿼리

인덱스가 ename, sal일 때

SELECT * FROM emp
WHERE sal > 9000
order by ename;

• 인덱스를 이용한 소트 연산 생략

   SELECT /*+ first_rows*/* FROM emp
   WHERE sal > 9000
   order by ename;

  • first_rows 힌트 사용
  • TABLE FULL SCAN보다 비효율적일 수 있지만 소트 연산을 생략할 수 있어서 전체 집합헤서 처음 일부를 빠르게 출력할 수 있다는 장점이 있다. -> 부분범위 처리가 가능한 상황에서 매우 효율적이다.

INDEX UNIQUE SCAN

수직적 탐색만으로 데이터를 찾는 스캔방식으로서 Unique 인덱스를 '='조건으로 탐색하는 경우에만 작동한다.

• Unique 인덱스가 존재하는 컬럼은 DBMS가 데이터 정합성 관리
• 모든 인덱스를 조건에 걸어줘야한다.

INDEX SKIP SCAN

오라클에서 인덱스 선두 컬림이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식

• 인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan방식을 취한다.
• 이 스캔 방식은 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용한다.
  • 예시로, 선택 컬럼은 성별, 후행 컬럼은 고객번호가 있다.
• 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는'리프 블록만 골라서 액세하는 스캔 방식.

SELECT * FROM 사원 WHERE 연봉 between 2000 and 4000

• Index Skip Scan

  • index_ss, no_index_ss 힌트 사용
  • 세번째 블록, 여섯번째 블록, 일곱번째 블록, 열번째 블록을 액세스한다.
    • 여섯번째 블록을 액세스하는 이유는 '여', 연봉 < 3000아래인 값을은 여기 저장될 것이라고 추측하기 때문입니다.
    • 열번째 블록을 액세스하는 이유는 '여'보다 값이 큰 미지의 성별 값이 존재한다면 모두 여기 저장되기 때문입니다.

• 선두 컬럼에 대한 조건절은 있지만 중간 컬럼에 대한 조건절이 없는 경우에도 Skip Scan을 사용할 수 있다.
  

  • 업종유형코드 01을 만족하고 20080501보다 크거나 같고 20080531보다 작거나 같은 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 가능

• Distinct Value가 적은 두 개의 선두 컬럼이 모두 조건절에 없는 경우에도 유용하게 사용할 수 있다.
  

• 선두 컬럼이 부등호, BETWEEN, LIKE같은 범위 검색 조건일 때도 Index Skip Scan을 사용할 수 있다.
  

  • Index Range Scan을 사용한다면, 기준일자 BETWEEN 조건을 만족하는 인덱스 구간을 '모두' 스캔해야하지만 Index Skip Scan을 사용한다면 BETWEEN조건을 만족하는 인덱스 구간에서 업종유형코드 = '01'을 포함할 가능성이 있는 리프 블록만 골라서 액세스할 수 있다.

INDEX FAST FULL SCAN

논리적인 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O방식으로 스캔하는 방식

• 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록들을 읽어들인다.

• 디스크로부터 대량의 인덱스 블록을 읽어야 할 때 큰 효과를 발휘한다. 하지만 인덱스 리프 노드가 갖는 연결 리스트 구조를 무시하고 데이터를 읽어서 결과집합이 인덱스 키 순서대로 정렬되지 않는다.

• 쿼리에 사용한 컬럼이 모두 인덱스에 포함되어 있을 때만 사용할 수 있다.

• 인덱스가 파티션 되어 있지 않더라도 병렬 쿼리가 가능하다.

• 관련 힌트 index_ffs, no_index_ffs

  

• Index Full Scan과 Index Fast Full Scan의 차이점
  

INDEX RANGE SCAN DESCENDING

인덱스를 뒤에서부터 앞쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다.

• 힌트 사용법 index_desc