오라클 인덱스 효율적으로 태우는 방법 [대용량 데이터 베이스 튜닝 및 설계 2일차]

오라클 인덱스 효율적으로 태우는 방법 [대용량 데이터 베이스 튜닝 및 설계 2일차]

 

 

평소에는 관심없게 생각했던 부분인데

 

이번 수업에선 정말 이건 필요하다 생각이 되는 군요

 

인덱스를 태울때 비교연산과 범위연산을 할 경우에 제일 효율적인 방법 입니다

 

간단하게 정리를 하자면 아래 순과 같습니다

 

( = )     >     ( >= )     >     ( between )

 

 

모든 값을 equal 비교를 할 경우가 제일 최상의 조건이지만 이렇게 테이블 구조를 잡을 수 가 없죠

 

만약 = 연산자와 범위 연산을 같이 사용 할 경우에는 범위연산을 제일 뒤에 두면 좋은 효과를 가질 수 있다고 합니다

 

= 연산자 가운데에 between 연산자가 들어갈 경우엔 결과셋이 흩어져 왔다 갔다 한다고 합니다

 

 

비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성 

 

 

 

 

인덱스 구성 컬럼이 모두 ‘=‘로 비교될 때는 모든 결과셋이 연속된다

    sql> … where col1 = 1 and col2 = ‘A’ and col3 = ‘나’ and col4 = ‘a’
 

 

 

선행 컬럼은 모두 ‘=‘이고, 마지막 컬럼만 범위 검색 조건 (부등호, between, like) 일때도 결과셋은 서로 모여 있다

    sql> … where col1 = 1 and col2 = ‘A’ and col3 = ‘나’ and col4 >= ‘a‘

 

 

중간 컬럼이 범위 검색 조건일 때는 결과셋이 흩어 진다

 
    sql> … where col1 = 1 and col2 = ‘A’ and col3 between ‘가’ and  ‘다’ and col4 = ‘a’

 

 

 

 

 

컬럼 1,2까지는 모여 있으나, 컬럼 3, 4를 만족하는 결과셋은 흩어진다.

 

선행 컬럼이 모두 ‘=‘조건인 상태에서, 처음 나타나는 범위 검색 조건 전까지를 만족하는 결과셋은 모두 연속되게 모여 있지만,

 

그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다

    sql> … where col1 = 1 and col2 <= ‘B’ and col3 = ‘나’ and col4 between ‘a’ and ‘b’

 

 

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

 

 

Sequential access의 효율은 선택도에 의해 결정됨.

인덱스 컬럼이 모두 ‘=‘조건일 때 선택도 가장 높다.

인덱스 선행 컬럼이 조건에 누락되거나 between, 부등호, like 같은 범위검색 조건이 사용되면 인덱스를 스캔하는 단계에서 비효율이 발생.

 

 

BETWEEN 조건을 IN-List로 바꾸었을 때 인덱스 스캔 효율

 

 

BETWEEN 절의 나쁜 경우는 1,2의 조건을 읽는 사이에 필요 없는 부분까지 읽게 되어 5개의 행을 읽게 되어 낭비가 된다

이를 equal로 바꾸어 union all을 할 경우에는 더 좋은 효과를 낼 수 있다 (그렇다고 모든 쿼리를 이렇게 바꾸면 무조건 효율적인건 아니다)

 

BETWEEN 조건을 IN-List, union all로 바꾸었을 때 인덱스 스캔효율 선행컬럼이 범위검색이면?
범위검색 컬럼이 선행컬럼일 경우 IN-List를 사용하면 효과가 있다.
결과셋이 멀리 떨어져 있을 경우 효과적이다

 

[BETWEEN 예시]

 

[BETWEEN을 in-list로 변경 예시] 

 

Between과 Like 스캔 범위 비교

 

Query 1의 예시가 제일 효율 적이다 부득이하게 200902의 B까지 불러 오지 않아 다른 쿼리와 비교 했을 때 제일 효율적이다. 

 

Query 1 : where 판매월 between ‘200901’ and ‘200902’ and 판매구분 = ‘A’;
Query 2 : where 판매월 like ‘2009%’ and 판매구분 = ‘A’;
Query 3 : where 판매월 >= ‘200901’ and  판매월 < ‘200903’ and 판매구분 = ‘A’;

 

 

 

 

인덱스 스캔을 하고 있더라도 쿼리의 속도를 떨어트릴 수 있는 요소가 되기때문에 이 부분이 정답이다 라고는 말씀드리지 않겠습니다.