인덱스 튜닝
테이블 액세스 최소화
SQL 튜닝은 랜덤 I/O와의 전쟁이다. 인덱스 ROWID는 논리적 주소다. 디스크 상에서 테이블 레코드를 찾아가기 위한 위치 정보를 담는다. 인덱스 ROWID는 우편주소이고 우체부 아저씨가 일일이 찾아다니는 구조이다. 메모리DB의 경우는 전화통신 구조이다.
인덱스 손익분기점
1. Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이다.
2. Table Full Scan은 Multiblock I/O인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 Sigle Block I/O
방식이다.
Table Full Scan은 성능이 일정하다. 1000만건중 한 건을 조회하든, 10만건을 조회하든 차이가 거의 없다.
인덱스를 이용해 테이블을 액세스할 때는 전체 1000만 건 중 몇 건을 추출하느냐에 따라 성능이 크게 달라진다.
인덱스 손익분기점은 보통 5~20%의 낮은 수준에서 결정된다. 인덱스 CF가 나쁘면 손익분기점은 5% 미만에서
결정되며 CF가 좋을 때는 손익분기점이 90% 수준까지 올라가기도 한다.온라인 프로그램 튜닝 vs 배치 프로그램 튜닝
1. 온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 중요하다.
조인도 대부분 NL 방식을 사용한다.
2. 대량 데이터를 읽고 갱신하는 배치프로그램은 항상 전체범위 처리 기준으로 튜닝해야 한다.
인덱스와 NL 조인보다 Full Scan과 해시 조인이 유리하다.
테이블을 두 번 읽는 것보다 윈도우 함수를 이용하는 것이 유리하다. 배치 프로그램에서는 파티션 활용 전략이
매우 중요한 튜닝 요소이다.
Covered 인덱스 : 인덱스만 읽어서 처리하는 쿼리에 사용한 인덱스
인덱스 구조 테이블(IOT, Index-Organized Table)
일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다. 즉 테이블 블록에 있어야 할 데이터를 인덱스
리프 블록에 모두 저장하고 있다. IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나다.
같은 값을 가진 레코드들이 100% 정력된 상태로 모여 있으므로 랜담 액세스가 아닌 시퀀셜 방식으로
테이터를 액세스한다.
인덱스 클러스터 테이블
인덱스 클러스터 테이블은 클러스터 키 값이 같은 레코드를 한 블록에 모아서 저장하는 구조다.
한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해서 클러스터 체인으로 연결한다.
해시 클러스터 테이블
해시 클러스터는 인덱스를 사용하지 않고 해시 알고리즘을 사용해 클러스터를 찾아간다는 점만 다르다.
부분범위 처리 활용
DBMS가 클라이언트에게 데이터를 전송할 때 일정량씩 나누어 전송한다. 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아있어도 서버 프로세스는 클라이언트로부터 추가 Fetch Call을 받기 전까지 그대로 멈춰 서서 기다린다. 1억건 짜리 테이블인데도 결과를 빨리 출력할 수 있는 이유이다.
정렬 조건이 있을 때 부분범위 처리
DB 서버는 '모든' 데이터를 다 읽고 정렬을 마치고서야 클라이언트에게 데이터 전송을 시작한다.
전체 범위 처리이다. 정렬 컬럼이 인덱스 선두컬럼에 있다면 부분 범위 처리가 가능하다.
ArraySize 조정을 통한 Fetch Call 최소화
배치 I/O
배치 I/O는 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안한 기능이다.
인덱스를 이용해 테이블을 액세스하다가 버퍼 캐시에서 블록을 찾지 못하면 일반적으로 디스크 블록을
바로 읽는데, 이 기능이 작동하면 테이블 블록에 대한 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량이
쌓이면 한꺼번에 처리한다.
배치 I/O 기능이 작동하면 인덱스를 이용해서 출력하는 데이터 정렬 순서가 매번 다를 수 있다
/*+batch_table_access_by_rowid(e)*/
인덱스 스캔 효율화
인덱스 엑세스 조건
인덱스 수직적 탐색을 통해 스캔 시작점을 결정하는데 영향을 미친다.
인덱스 필터 조건
테이블로 액세스할지를 결정하는 조건절이다.
BETWEEN을 IN-List로 전환
범위 검색을 하던 것을 수직적 탐색 + FILTER로 큰 효과를 얻는 경우가 있다.
(조건을 만족하는 레코드가 서로 멀리 떨어져 있을 때만 BETWEEN 조건을 IN-List로 전환하는 기법이 유용하다)
IN-List 개수가 늘어나는 경우는 NL 방식의 조인문이나 서브쿼리로 구현하면 된다.
Index Skip Scan 활용
IN 조건
IN-List Iterator 방식으로 풀리는 것이 유리한 경우는 filter하는 데이터가 멀리 떨어져 있는 경우이며
한곳에 모여 있다면 filter로 스캔하는 것이 유리하다.
/*+num_index_keys(테이블 인덱스 인덱스_컬럼_숫자)*/ --> filter 혹은 IN-List로 유도
BETWEEN과 LIKE 스캔 범위 비교
LIKE보다 BETWEEN을 사용하는 게 낫다. 범위를 더 적게 스캔할 것이므로.
OR 조건을 활용한 옵션 조건 처리
1)인덱스 액세스 조건으로 사용 불가
2)인덱스 필터 조건으로 사용 불가
3)테이블 필터 조건으로 사용 가능
4)단, 인덱스 구성 컬럼 중 하나 이상이 NOT NULL 컬럼이면, 18C부터 인덱스 필터 조건으로 사용 가능
LIKE/BETWEEN 사용시 점검해야 할 내용
1.인덱스 선두 컬럼 : 선두컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하는 것은 금물이다.
2.NULL 허용 컬럼 : LIKE/BETWEEN 연산자에 NULL입력시, NULL 값이 있는 집합 결과는 누락됨
3.숫자형 컬럼 : LIKE 조건 컬럼이 형변환되어 인덱스 액세스 조건이 아니라 필터 조건으로 형변환된다.
4.가변길이 컬럼 : 박훈을 검색하고 싶었으나 박훈남도 검색결과로 나올 수 있다
함수호출부하 해소를 위한 인덱스 구성
PL/SQL 사용자 정의 함수가 느린 이유
1. 가상머신 상에서 실행되는 인터프리터 언어
함수와 프로시저 컴파일 시 바이트코드를 생성해서 데이터 딕셔너리에 저장
PL/SQL 엔진은 바이트코드를 런타임 시 해석하면서 실행한다.
2. 호출 시마다 컨텍스트 스위칭 발생
실행시 매번 SQL 실행엔진과 PL/SQL 가상머신 사이에 컨텍스트 스위칭이 일어난다.
3. 내장 SQL에 대한 Recursive Call 발생
가장 결정적인 요소는 Recurisve Call이다. 결과가 100만건이면 function이 100만번 호출된다.
funciton보다 스칼라쿼리가 성능적으로 훨씬 우수하다.
조건절에 function을 사용하는 경우 테이블 데이터건수 만큼 실행된다. function을 사용해서 index를 만들면
index access가 가능하다.
인덱스 설계
인덱스 설계의 가장 중요한 두 가지 선택 기준
1. 조건절에 항상 사용하거나, 자주 사용하는 컬럼
2. '=' 조건으로 자주 조회하는 컬럼을 앞쪽에 둔다.
그 외 고려해야 할 판단 기준
1. 수행빈도
2. 업무상 중요도
3. 클러스터링 팩터
4. 데이터량
5. DML 부하
6. 저장 공간
7. 인덱스 관리 비용 등
NL조인시 INNER쪽 인덱스 효율 높이기
OUTER쪽 인덱스는 1번 사용되지만 INNER쪽 인덱스는 OUTER쪽 결과수만큼 인덱스를 사용한다.
소트 연산을 생략하기 위한 컬럼 추가
1. '=' 연산자로 사용한 조건절 컬럼 선정
2. ORDER BY 절에 기술한 컬럼 추가
3. '=' 연산자가 아닌 조건절 컬럼은 데이터 분포를 고려해 추가 여부 결정
IN 조건이 있다면 IN-List Iterator 방식으로 풀려서 IN 조건절은 인덱스 뒤로 보내야 한다.
선택도(조건절에 의해 선택되는 레코드 비율)가 높은 인덱스는 생성해봐야 효용가치가 별로 없다.
테이블 액세스가 많이 발생하기 때문이다.
카디널리티: 선택도 * 총레코드수
컬럼 순서 결정시 선택도가 높은 컬럼을 뒤에 두는 것이 효율적일까? 답은 상관 없다이다.
둘 다 인덱스 조건이므로 어떤 컬럼이 앞으로 오든 인덱스 스캔 범위는 똑같다.
결론적으로, 인덱스 생성 여부를 결정할 때는 선택도가 매우 중요하지만, 컬럼간 순서를
결정할 때는 각 컬럼의 선택도보다 필수 조건 여부, 연산자 형태가 더 중요한 판단 기준이다.
