by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

1.2 인덱스의 적용원칙




1.2 인덱스의 적용원칙

양호한 엑세스 경로를 보장받기 위한 조건
  • 좋은 액세스 경로가 생성될 수 있도록 테이블을 설계.
  • 적절하고 종합적인 경우를 대비한 인덱스의 지정.
  • 넓은 범위의 처리나 조인의 효율성을 향상하기 위한 클러스터링.
  • 인덱스 적용원칙에 맞는 SQL코딩. - 효율적인 SQL구사.
  • 통계 자료의 주기적인 재생성. - 힌트나 사용제한(Suppressing) 기능의 활용.
인덱스를 사용하지 않는 경우
  • 인덱스 컬럼이 비교되기 전에 변형이 일어날 경우.
    → SELECT deptno FROM dept WHERE SUBSTR(dname,1,3) = 'ABC'
  • 부정형(NOT, <>)으로 조건을 기술한 경우.
    → SELECT ename FROM emp WHERE job <> 'SALES'
  • 인덱스 컬럼이 NULL로 비교되는 경우(NULL, NOT NULL의 사용)
    → SELECT * FROM emp WHERE ename IS NOT NULL
  • 옵티마이져가 특정 인덱스의 사용을 취사 선택할 경우 사용되지 않을 수 있음
    조건의 순위(Ranking), 사용자의 힌트, 통계정보에 의거하여 산출된 액세스비용 등등..
    → SELECT * FROM emp WHERE job LIKE 'AB%' AND empno = '7890

가. 인덱스컬럼의 변형(Suppressing)

(1) 외부적(External) 변형

SELECT dept, ename
  FROM emp
 WHERE SUBSTR(job, 1, 4)='SALE'




SELECT dept, ename
  FROM emp
 WHERE job LIKE 'SALE%'




SELECT dept, ename
  FROM emp
 WHERE sal*12 = 100000




SELECT dept, ename
  FROM emp
 WHERE sal = 100000/12




SELECT dept, ename
  FROM emp
 WHERE NVL(job, 'X') = 'SALE'




SELECT dept, ename
  FROM emp
 WHERE job = 'SALE'




  • 인덱스의 특징을 역으로 이용하여 수행속도를 향상.
    => 'CUSTNO'와 'STATUS'는 각각 인덱스가 생성되어 있고 'STATUS'가 '90'인 경우에는 분포도가 넓다고 가정하면..

    SELECT custno, chuldate
      FROM chulgot
     WHERE custno = 'DN01'
       AND status = '90'
    


    SELECT custno, chuldate
      FROM chulgot
     WHERE custno = 'DN01'
       AND RTRIM(status) = '90'
    


    => ORD_DATE LIKE '9502%'를 만족하는 로우수가 ORD_DEPT = '12345'를 만족하는 로우 수보다 적다고 가정 했을 경우..

    SELECT x.ordno, x.ord_date, y.item, y.ordqty
      FROM ORDER1T x,  ORDER2T y
     WHERE x.ordno = y.ordno
       AND x.ord_date LIKE '9502%'
       AND y.orddept =  '12345'
     ORDER BY ord_date
    


    SELECT x.ordno, x.ord_date, y.item, y.ordqty
      FROM ORDER1T x,  ORDER2T y
     WHERE x.ordno = y.ordno
       AND x.ord_date   LIKE  '9502%'
       AND RTRIM(y.orddept  =  '12345')
    ORDER  BY  ord_date
    


    => sale_dept 가 95년도인 데이터가 아주 많고, sale_dept로 생성된 인덱스가 있다고 가정 했을 경우..

    SELECT sal_no, sale_date, sale_dept, saleqty
      FROM mechult
     WHERE sale_date LIKE '95%'
     ORDER BY sale_dept
    


    SELECT sal_no, sale_date, sale_dept, saleqty
      FROM mechult
     WHERE RTRIM(sale_date) LIKE '95%'
       AND sale_dept > ''
    


(2) 내부적 변형

  • JOIN시 데이터 타입의 통일
    SELECT chr,num,var,dat
      FROM samplet
      WHERE chr = 10
    

    ===>

    SELECT chr,num,var,dat
      FROM samplet
      WHERE to_number(chr) = 10
    
    => 문자타입을 숫자와 비교: 숫자로 변형.
    => 숫자타입을 문자와 비교: 형변환 없음.(단, like '10%'와 같이 비교하는 경우 문자로 형변환 됨)
  • 그럼 여기서 질문....(DAT컬럼은 Data type)
SELECT *
FROM SAMPLET
WHERE  DAT =  '01-JAN-94'

나. 부정형비교

  • 부정형을 긍정형으로 유도

    SELECT 'Not found'
      FROM EMP
     WHERE EMPNO <> 7369
    


    SELECT 'NOT FOUND'
    FROM DUAL
    WHERE NOT EXISTS
                    ( SELECT 'X'   FROM EMP
                       WHERE EMPNO = 7369 )
    


    SELECT 'Not found'
      FROM emp a
     WHERE NOT EXISTS
                     (SELECT empno  FROM emp b
                       WHERE b.empno = 7369
                         AND a.empno = b.empno)
    


=> TAB1테이블의 'YYYYMM','COL1'가 각각 인덱스로 생성되어 있고 TAB2의 'YYYYMM','COL2'가 각각 인덱스로 생성되어 있다

1)
SELECT *
  FROM TAB1
 WHERE YYYYMM = '199910'
 AND NOT EXISTS ( SELECT *
                            FROM TAB2
                             WHERE COL2 = COL1
                               AND YYYYMM = '199910')




2)
SELECT *
  FROM TAB1
 WHERE YYYYMM ='199910'
   AND COL1 NOT IN (SELECT COL2
                      FROM TAB2
                     WHERE YYYYMM = '199910')




3)
SELECT *
FROM TAB1
WHERE (YYYYMM, COL1) IN (SELECT '199910', COL1
                           FROM TAB1
                          WHERE YYYYMM = '199910'
                          MINUS
                         SELECT '199910', COL2
                           FROM TAB2
                          WHERE YYYYMM = '199910')





1) 과 2) 에서의 sub쿼리는 나중에 수행되거나 check조건으로 수행된다.
3) 인 경우엔 서브쿼리에서 MINUS결과를 먼저 수행하고, 그 결과를 가지고 메인쿼리를 엑세스 한다.
각각 테이블에 'YYYYMM+COL1','YYYYMM+COL2'로 구성된 인덱스가 있다면 테이블을 엑세스 하지 않고 인덱스만 가지고 sort merge방식으로 서브쿼리를 먼저 수행한 후 처리된 서브쿼리의 결과로 메인쿼리의 조건으로 사용한다.



다. NULL을 사용한 비교

(1) NULL컬럼의 적용

SELECT *
  FROM emp
 WHERE ename IS NOT NULL




SELECT *
  FROM emp
 WHERE ename > ''




SELECT *
  FROM emp
 WHERE empno IS NOT NULL




SELECT *
  FROM emp
 WHERE empno > 0




  • 옵티마이져 모드가 Rule_based 이거나 Cost_based 모드에서 'FIRST_ROWS'로 설정되었을 때 가능하다.
    SELECT ord_dept, ord_date, SUM(ord_qty), COUNT(*)
    FROM?? order1t
    WHERE? ord_no > 0
    GROUP? BY ord_dept
    ???????? ,ord_date
    ORDER? BY ord_date
    
    • SUM, COUNT, MAX, MIN, AVG등의 그룹함수를 사용했거나, GROUP BY, ORDER BY, UNION, MINUS, INTERSECT등을 사용하면 전체범위를 인덱스를 경유함으로 인덱스를 사용하는 것이 훨씬 불리하다.

    SELECT *
      FROM emp
     WHERE ename IS NULL
    


    CREATE TABLE emp(
       ename VARCHAR2(20) DEFAULT '00'
       ......
    )
    


  • 분포도가 양호할 경우에는 Default 사용이 유리, 그렇지 않을 경우에는 null 값을 가지고 있는 것이 유리하다.

(2) NULL공포증의 해소

  • NULL이란?
  • NULL도 1, A와 같은 하나의 값이다.
  • 어떤 값보다 크지도 않고 작지도 않다.'
  • 그러므로 어떤 값과 비교 될 수도 없다.
  • 즉 NULL과 연산 결과는 NULL이 된다.
SELECT ord_dept, SUM(ordqty), AVG(ordqty+asnqty), AVG(ordqty)
  FROM ORDER
 WHERE status < 'C'
 GROUP BY ord_dept
  • status가 null인 값은 작업대상에서 빠진다.
  • AVG(ordqty+asnqty)는 ordqty나 asnqty 중 하나만 NULL을 가진다면 처리대상에서 제외된다.
  • SUM(ordqty) 와 SUM(nvl(ordqty,0)) 은 동일하다.(결과는 동일하나 후자는 불필요한 연산을 수행하므로 불리함)
  • AVG(ordqty+asnqty) 와 AVG(nvl(ordqty,0)+nvl(asnqty,0))는 서로 다르다.
  • AVG(ordqty) 에서 ordqty에 NULL이 있을때와 없을때의 차이점??
    NULL 공포증의 해소 방안
    '확정은 되었으나 값이 없다' 경우에는 문자 타입일 때는 ' '(Space)나 기타 문자를 필요에 따라 지정하고, 숫자 타입일 때는 0 을 지정한다.
    '미확정'인 값도 하나의 의미를 지닌 값이라고 보아야 한다는 것이 NULL 값이 만들어진 이유이다.
    테이블을 생성시 DEFAULT 제약조건을 이용해서 기본값을 지정하여 처리 할 수도 있다.
    NVL함수의 사용으로 인한 불필요한 연산 보다는 NULL값에 대한 일관성을 유지하는 것이 필요하다.

라. 옵티마이져에 의한 취사선택

(1) 순위(Ranking)의 차이

SELECT ord_dept, ordqty
  FROM ORDER1T
 WHERE status = 'C'
   AND ord_date like '9502%'
  • RBO: 순위에 의해서 ord_date 인덱스는 무시되고, status인덱스를 사용.
  • CBO: 분포도에 따라 옵티마이저가 선택.
SELECT ord_dept, ordqty
  FROM ORDER1T
 WHERE status = 'C'
   AND ord_date = '950201'
  • 각 컬럼이 별개의 인덱스로 생성이 되어 있다면 인덱스 머지를 일으킴
SELECT ord_dept, ordqty
  FROM ORDER1T
 WHERE ord_dept like '12%'
   AND ord_date like '9502%'
  • 두개 인덱스중 하나만 사용함.
  • RBO: 나중에 생성된 컬럼 사용.
  • CBO: 분포도에 따라 옵티마이저가 선택.
    범위 처리가 넓다고 할 수 있는 'LIKE', 'BETWEEN', '<', '>'등과 같이 사용될 경우 결코 인덱스 머지를 하지 않고 어느 하나의 인덱스만 사용하고 나머지는 포기한다.

(2) 낮은 처리비용의 선택

SELECT *
  FROM emp
 WHERE ename > 'A'
  • FIRST_ROWS 로 설정되었을 때 인덱스를 사용하지만 ALL_ROWS인 경우에는 전체 테이블을 스캔 함.

(3) 힌트(Hint)에 의한 선택

  • 옵티마이져가 액세스 경로를 결정할 때 옵티마이져에게 모든 것을 맡기지 않고 사용자가 원하는 보다 좋은 접근경로를 직접 선택해서 최적의 튜닝을 할 수 있도록 도와줌
    힌트의 사용 방법
    /*+ */ : 힌트의 내용을 여러 라인에 걸쳐서 기술할 수 있음.
    --+ : 오직 한 라인에만 기술해야 하며, 컬럼은 반드시 다음 라인에 기술해야 함.
    ORACLE_Hint 정리
    HINT_Dictionray.xls

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.