DB 튜닝에 대한 얇고 넓은 지식

▶ 인덱스가 있어도 효율적 활용이 불가능한 경우

1) 인덱스 선두컬럼, 조건절을 가공했을 경우.

SELECT * FROM EMP WHERE SUBSTR(TO_CHAR(EMP_NO), 1, 4) = '2012';

2) 부정형 비교

SELECT * FROM EMP WHERE EMP_NO != '20120902;

3) Not null 컬럼 검색

SELECT * FROM EMP WHERE DEPT_CD IS NOT NULL;

▶ 인덱스 컬럼 가공에 대한 튜닝 예시

AS-IS

SELECT * FROM EMP WHERE substr(이름, 1, 2) = '김길';

TO-BE

SELECT * FROM EMP WHERE 이름 like '김길%';

AS-IS

SELECT * FROM 사원 where 월급여 * 12 = 40,000,000;

TO-BE

SELECT * FROM 사원 where 월급여 = 40,000,000 / 12;

AS-IS

SELECT * FROM 주문 where to_char(일시, 'yyyymmdd') = '20190101;

TO-BE

SELECT * FROM 주문 where 일시 >= to_date('20190101', 'yyyymmdd') and 일시 < to_date('20190101', 'yyyymmdd') +1;

▶ 문자타입의 비교

1) 양쪽 모두 CHAR 타입인 경우

-. 두 컬럼의 길이를 비교하여 짧은 쪽의 컬럼에 공백을 추가하여 길이를 같게 한 후 비교

-. 서로 다른 문자가 나올 때까지 비교하여 다른 값이 나오면 문자 값이 큰 컬럼이 크다고 판단하고 비교를 종료

-. 값이 같다면 길이에 상관없이 비교는 정상적으로 수행되나 뒤에 공백만 있더라도 길이가 긴 컬럼의 길이만큼 비교작업을 수행하므로 수행속도가 나빠짐

-. 따라서, 가급적이면 데이터 길이가 틀린 컬럼을 비교하는 것을 지양해야 한다. 

2) 어느 한 쪽에 Varchar 타입이 있는 경우

-. 문자 값 비교가 먼저 수행됨

-. 서로 다른 문자가 나올 때까지 비교하여 다른 값이 나오면 문자 값이 큰 컬럼이 크다고 판단하고 비교를 종료함

-. 값이 같다면 길이가 짧은 컬럼의 길이만큼 비교한 후, 각 컬럼의 길이를 비교하여 긴 컬럼이 크다고 판단함

-. char 타입과 varchar 타입 간의 비교 작업은 저장된 결과는 같더라도 '같지 않다'라는 결과가 나오므로 char 타입은 사용하되 반드시 약속이 선행된 상태에서만 사용

3) 양쪽 모두 Varchar2 타입인 경우

-. 문자 값 비교가 먼저 수행됨

-. 서로 다른 문자가 나올 때까지 비교하여 다른 값이 나오면 문자 값이 큰 컬럼이 크다고 판단하고 비교를 종료함

-. 값이 같다면 지정된 컬럼의 길이에 관계 없이 동일한 길이를 가지므로 같은 결과가 나옴

4) 상수 값과의 비교

-. 상수 값은 문자 값이지만 char인지 varchar2인지 모르므로 상수 쪽의 데이터 타입을 변수의 데이터 타입과 같아지도록 변화하여 위 1, 2, 3 중 해당하는 경우의 비교법칙이 적용됨

-. 서로 비교를 해야 하는 경우, 같은 데이터 타입으로 통일하되 varchar2 타입 사용을 권고함

▶ 인덱스 적용 원칙

인덱스가 사용되지 않는 경우는 다음과 같다.

-. 비교되기 전에 인덱스 컬럼의 내부적인 변형, 외부적인 변형이 일어나는 경우. 즉, 좌변을 변형하면 인덱스가 적용되지 않는다.

-. 부정형으로 기술한 경우.

-. 인덱스 컬럼이 Null로 비교되는 경우. 인덱스 첫번째 컬럼이 null로 비교되면 사용될 수 없다.

-. 컬럼의 값이 null인 로우는 인덱스에 저장되지 않는다.(이건 dbms마다 틀리니 확인이 필요) 따라서, 결합 인덱스의 첫번째 컬럼이 아닌 값을 null로 비교하는 경우에는 인덱스 사용

▶ 인덱스 선정

인덱스는 테이블의 특성, 생성시키고자 하는 컬럼의 분포도, 처리범위 등을 정확히 파악하여 지정해야 한다. 특정 애플리케이션에만 영향을 미치는 것이 아니라 그 컬럼을 사용하는 모든 경우에 영향을 미치므로 막연한 추측을 통해 결정해서는 안되며 가능한 실측자료를 토대로 액세스의 빈도, 처리범위의 크기, 분포도, 테이블의 크기, 액세스 유형 등을 감안하여 종합적이고 전략적으로 결정해야 한다.

1) 6블록 이상의 테이블에 적용

2) 컬럼의 분포도가 10~15% 이내인 경우에 적용한다.

3) 분포도가 범위 이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토한다.

4) 분포도가 범위 이상이더라도 부분 범위 처리를 목적으로 하는 경우에는 인덱스 적용

▶ 인덱스 컬럼의 선정

1) 분포도와 손익분기점

-. 컬럼의 분포도가 10~15% 이내인 경우

-. 분포도 = (1/컬럼값의 종류) * 100 = (컬럼값의 평균 로우수 / 테이블의 총 로우수) * 100

2) 결합 인덱스의 특징

-. Col1 + Col2 순으로 인덱스 생성한 경우, col1 의 분포도가 넓으나 '='로 사용된 col1 이 앞에 위치하고 분포도는 좁으나 'between'으로 사용된 col2 가 뒤에 위치한 결합 인덱스를 생성시킨 경우.

-. col2+col1 순으로 인덱스 생성한 경우 인덱스의 첫번째 컬럼이 '='로 사용했다 하더라도 처리범위는 줄어들지 않는다. '=' 조건은 단지 테이블을 액세스 할 것인지 말 것인지를 판단하는 역할

-. 대다수의 액세스 형태를 만족하기 위해 col2+col1 순으로 결합인덱스 생성했다고 가정

-. 가끔은 col2 는 like 나 between 등으로 사용되고 col1 은 '='로 사용되는 경우가 있다면 'col1+col2' 로 인덱스를 구성한 경우에 비해 불리