인덱스 컬럼의 변경

 

인덱스 컬럼의 변형은 결국 인덱스를 사용하지 못하게 되어 수행속도에 많은 악 영향을 끼친다.

여기에서는 인덱스 컬럼의 외부적(External) 변형에 대해서 알아보고 그 대체방안에 대해서 알아본다.

 

(KEY WORD : INDEX 활용, 인덱스, 외부적인변형, Suppressing )

 

 

• 인덱스 컬럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후에 비교된다. 하지만 인덱스는 가공되기 전의 값으로 생성되어 있기 때문에 당연히 인덱스를 사용할 수 없게 된다.
• 여기에서 외부적(External) 변형이란 사용자가 인덱스를 가진 컬럼을 어떤 SQL함수나 사용자 지정함수(User Defined Stored Function), 연산, 결합(||) 등으로 가공을 시킨 후에 발생되는 것이며 이러한 거의 모든 경우는 변형이 일어나지 않도록 다시 기술할 수 있다.
• 다음의 외부적 변형에 대한 예와 대체방안에 대해서 여러가지 예를 통해서 알아보자.
• SUBSTR(JOB,1,4)
  
  SELECT DEPT, ENAME, SAL
  FROM EMP
  WHERE SUBSTR(JOB,1,4) = ‘SALE’
  
          SELECT DEPT, ENAME, SAL
          FROM EMP
          WHERE JOB LIKE ‘SALE%’
• SAL * 12
  
  SELECT EMPNO, ENAME, JOB
  FROM EMP
  WHERE SAL * 12 = 35000000
  
          SELECT EMPNO, ENAME, JOB
          FROM EMP
          WHERE SAL = 35000000 / 12
• TO_CHAR(HIREDATE,’YYYYMMDD’)
  
  SELECT EMPNO, ENAME, JOB
  FROM EMP
  WHERE TO_CHAR(HIREDATE , ‘YYYYMMDD’) = ‘19990101’
  
          SELECT EMPNO, ENAME, JOB
          FROM EMP
          WHERE HIREDATE = TO_DATE(‘19990101’,’YYYYMMDD’)
• 다음은 NULL에 대한 잘못된 사용에 대한 예이다.

            SELECT EMPNO, ENAME, JOB
            FROM EMP
            WHERE NVL(JOB,’X’) = ‘CLERK’

                    SELECT EMPNO, ENAME, JOB
                    FROM EMP
                    WHERE JOB = ‘CLERK’

• JOB || DEPT

            SELECT EMPNO, ENAME, JOB
            FROM EMP
            WHERE JOB || DEPT = ‘CLERK10’

                    SELECT EMPNO, ENAME, JOB
                   FROM EMP
                    WHERE JOB = ‘CLERK’
                      AND DEPT = ’10

• COL1 || COL2 = :FLD

            SELECT *
            FROM TAB1
            WHERE COL1 || COL2 = :FLD

                    SELECT *
                    FROM TAB1
                    WHERE COL1 = SUBSTR(:FLD,1,3)
                      AND COL2 = SUBSTR(:FLD,4,2)

 

• 위의 예외의 다른 예들도 많이 존재한다. 여기에서 우리가 주의 해야할 점은 조건으로 주어진 컬럼을 변형하지 말고 조건값을 변형하는 방향으로 SQL을 작성하는 것이 중요하다.
• 더 자세한 내용은 ‘대용량 데이터베이스 솔루션 I page 8’을 참조하기 바란다.

 

인덱스 컬럼의 내부적 변형

 

인덱스 컬럼의 변형은 결국 인덱스를 사용하지 못하게 되어 수행속도에 많은 악 영향을 끼친다. 여기에서는 인덱스 컬럼의 내부적(Internal) 변형에 대해서 알아보고 그 대체방안에 대해서 알아본다.

  

(KEY WORD : INDEX 활용, 인덱스, 내부적인 변형, Suppressing )

 

다음과 같은 테이블이 있다고 가정한다.

　       CREATE TABLE SAMPLET
           ( CHR CHAR(10),
             NUM NUMBER(12,3),
             VAR VARCHAR2(20),
             DAT DATE )

 

• 인덱스 컬럼의 변형은 사용자가 직접 컬럼을 가공시켜 코딩하지 않았더라도 서로 다른 데이터 타입을 비교하고자 할 때 DBMS가 어느 한 쪽을 기준으로 동일한 타입이 되도록 내부적인 변형을 일으키게 하므로 자신도 모르게 인덱스를 사용하지 못하는 상황이 된다.
   
• 다음은 내부적 변형에 대한 예와 그에 대한 해결책을 예를 들어 설명한다.
   
• 상수 부분은 숫자이고 비교되는 ‘CHR’ 컬럼은 문자 타입이므로 숫자를 기준으로 문자 타입이 숫자 타입으로 변형된다.

                SELECT CHR, NUM, VAR, DAT
                FROM SAMPLET
                WHERE CHR = 10

                     SELECT CHR, NUM, VAR, DAT
                      FROM SAMPLET
                      WHERE CHR = ‘10’

• 위 예에서 처럼 내부적인 변형은 사소한 잘못으로 인해 발생하는 경우가 대부분이다. 그래서 가능한 한 컬럼의 데이터 타입을 결정할 때 같이 비교해야 하는 컬럼의 타입은 반드시 일치시켜야 한다.
   
• 또한 이런한 내부적인 컬럼의 변형을 방지하기 위해서 위의 예와 같이 상수값의 타입은 문자 타입으로 기술하는 습관을 들여야 한다.
   
• 상수 값이 문자 타입이든 숫자 타입이든 컬럼의 타입이 숫자인 경우에는 내부적인 변형이 일어나지 않고 그 숫자를 기준으로 상수 값을 변형한다. 그렇다고 컬럼을 가능하면 숫자 타입으로 지정하는 것이 올은 일인가? 다음의 예를 보자.

                 SELECT CHR, NUM, VAR, DAT
                 FROM SAMPLET
                 WHERE NUM LIKE ‘10%’

• 앞에서 설명한 원칙대로라면 ‘NUM’ 컬럼이 숫자 타입이므로 ‘10%’ 가 숫자로 변해야 한다. 하지만 ‘%’라는 문자값이 있으므로 숫자 타입으로 바꿀 수 없다. 이런 경우에는 상수 값(‘10%’)을 기준으로 숫자 컬럼이 변형되는 결과를 초래 한다. 그래서 ‘NUM’ 컬럼에 대한 인덱스가 있더라도 사용하지 못하는 상황이 발생된다.

• 결론적으로 보면 SQL 코딩시 비교하는 상수 값을 함부로 숫자 타입으로 하지 않도록 해야 하며, 특히 조인의 연결 고리가 되는 컬럼들의 데이터 타입은 서로 다르지 않도록 해야 한다.
   
• 더 자세한 내용은 ‘대용량 데이터베이스 솔루션 I page 13’을 참조하기 바란다.