[SQL튜닝] 인덱스 활용이 불가능한 경우

[SQL튜닝] 인덱스 활용이 불가능한 경우

---

주요내용

• 인덱스를 사용하지 말아야 하는 경우
• 인덱스 사용이 불가능한 경우
• 옵티마이저에 의한 선택 절차

학습목표

• 인덱스 사용이 불가능한 경우에 대한 이해를 바탕으로 비효율적으로 수행되는 SQL 문장을 진단할 수 있다.
• 인덱스 사용이 불가능한 경우, 효율적인 SQL 문장으로 개선할 수 있다.
• 인덱스 사용이 불가능한 경우, 개선한 SQL 문장이 성능목표에 부합하는지 테스트할 수 있다.

---

인덱스를 사용하지 말아야 하는 경우

• 인덱스 스캔을 하면 무조건 빠른가? => 조건에 의한 처리범위가 넓어짐으로 인해 분포도가 나빠지는 경우가 있는데, 이 경우 인덱스 스캔을 하는 것 보다는 FULL TABLE SCAN을 하는 것이 바람직함
  • FULL TABLE SCAN 시엔 한 번의 I/O 때 마다 여러 개의 데이터 Blocks를 처리하기 때문에 I/O 횟수가 감소하게 됨

    DB_FILE_MULTIBLOCK_READ_COUNT = 4

---

인덱스 사용이 불가능한 경우

• NOT 연산자 사용

  SELECT * FROM emp WHERE empno != 7654;
  SELECT * FROM emp WHERE empno <> 7654;

• IS NULL, IS NOT NULL 사용

  SELECT * FROM emp WHERE empno is NULL;

• 옵티마이저의 취사 선택
  • 옵티마이저(Optimizer)의 자의적 판단에 의해서 인덱스를 사용할 수도 있고 사용하지 않을 수도 있는데, 이러한 것을 취사선택 이라고 함
  • Rule Base Optimizer : 정해진 규칙 기준
  • Cost Base Optimizer : 비용 기준
  • Optimizer의 자의적 판단으로 인한 잘못된 선택을 강제로 제어하기 위해서 Hint 사용
• External suppressing
  1) 불필요한 함수를 사용한 경우

  WHERE SUBSTR(ename, 1, 1) = 'M';

  WHERE ename LIKE 'M%';

  2) 문자열 결합

  WHERE job||deptno = 'MANAGER10';

  WHERE job = 'MANAGER' AND deptno = 10;

  3) DATE 변수의 가공

  WHERE TO_CHAR(hiredate, 'YYYYMMDD') = '20021016';

  4) 산술식의 적용

  WHERE sal*12 > 40000;

  WHERE sal > 40000/12;

• Internal suppressing
  • VARCHAR2 or CHAR to NUMBER
  • VARCHAR2 or CHAR to Date
    1) 간단한 연산식

    comm + '500'

    2) 논리비교 연산식

    bonus > sal/'10'

    3) 함수호출

    MOD(sal, '100')

    4) WHERE절 조건

    WHERE hiredate = '2003-01-01';

    WHERE ROWID = 'AAAAaoAATAAAADAAA';

    5) WHERE 문자형 컬럼 (resno, VARCHAR2) = 숫자정보
    eg. WHERE resno = 7402191550521

    WHERE TO_NUMBER(resno) = 7402191550521

---

옵티마이저에 의한 선택 절차

• 특정 테이블에 대해서 SQL의 주어진 조건으로 인해 사용될 수 있는 인덱스가 두 개 이상인 경우
  • 옵티마이저는 조건에 가장 적절한 인덱스를 선택해서 사용해야 함 => 주어진 조건에 가장 적절한 인덱스를 선택하려 할 때, 일련의 절차에 따라 결정함
• 예시

  SELECT * FROM sales
  WHERE 부서 = '843'
  AND 기준일자 = '970518'
  AND 품목 = 'B023';

• 옵티마이저의 인덱스 선택 시 판단 절차
  1) 주어진 조건에 대한 각 인덱스 별로 매칭율을 계산해서 매칭률이 높은 것을 우선적으로 선택함 => 인덱스 매칭률 = (WHERE절에서 첫번째 컬럼부터 연속된 컬럼에 대해 상수(값)를 '='로 비교하는 컬럼의 개수) / 인덱스를 구성하는 컬럼의 총 개수
  2) 인덱스 별 매칭률이 같을 경우에는 인덱스를 구성하는 컬럼의 개수가 많은 것을 우선적으로 선택함
  3) 인덱스 별 매칭률과 인덱스를 구성하는 컬럼의 개수가 같을 경우에는 가장 최근에 생성된 것을 우선적으로 선택함
• RBO와 CBO가 선택한 인덱스의 차이

  [인덱스 현황]
  EC_COURSE_SQ_PK : COURSE_CODE + YEAR + COURSE_SQ_NO
  EC_COURSE_SQ_IDX_01 : YEAR(Non Unique)
  SELECT MIN(course_sq_no) AS min_sq,
       MAX(course_sq_no) AS max_sq
  FROM ec_course_sq
  WHERE course_code = 1960
  AND year = '2002';