SQL 튜닝의 시작 (2013년)
목록
컬럼에 불필요한 ORACLE 제공함수를 사용하지 말자 0 0 99,999+

by 구루비스터디 컬럼변형 [2018.07.14]

컬럼에 불필요한 ORACLE 제공함수를 사용하지 말자


성늠문제의 대표적인 두가지 유형은
  • 첫 번째는 조회 컬럼과 변수의 데이터 타입이 다른경우.
  • 두 번째는 SQL의 조회 컬럼에 ORACLE에서 제공하는 함수를 사용하는 경우.


부적절하게 FUNCTION을 컬럼에 사용하는 경우


<CASE 1 CUSTNO데이터 타입이 CHAR일 때 >
  • 변경 전 : WHERE SUBSTR(CUSTNO,1,7) ='1234567'
  • 변경 후 : WHERE CUSTNO LIKE '1234567'||'%'


<CASE 2 INSERT_DATE 데이터 타입이 DATE일 때>
  • 변경 전 : WHERE TO_CHAR(INSERT_DATE,'YYYYMMDD') ='20110125'
  • 변경 후 : WHERE INSERT_DATE >= TO_DATE('20110125','YYYYMMDD') AND INSERT_DATE < TO_DATE('20110125','YYYYMMDD') + 1


  • 위와 같은 WHERE 조건절을 변경하지 못하는 환경에서는 차선책으로 FBI(FUNCTION BASED INDEX)을 생성하여 성능을 개선 할 수 있다.


<생성 방법>

CREATE INDEX INDX_01 ON T1(SUBSTR(CUSTNO,1,7));
CREATE INDEX INDX_01 ON T1(TO_CHAR(INSERT_DATE,'YYYYMMDD'))


  • FUNCTION BASED INDEX는 CASE 1 , CASE 2와 같이 주로 컬럼 변형에 의해 인덱스를 사용하지 못하는 경우에 성능을 개선하기 위해 생성한다.
  • 하지만 때로는 자주 사용하는 온라인 프로그램에서 ORDER BY 절에 사용되는 정려과 동일한 인덱스를 생성할 목적으로 생성하기도 한다.
  • 왜냐하면, ORDER BY 절과 인덱스가 동일하게 정렬되어 있다면, 부분범위 처리로 유도할 수 있기 때문이다.


컬럼 변형으로 인해 인덱스의 MIN/MAX를 활용하지 못하는 경우


SQL에 사용되는 컬럼이 모두 인덱스 구성에 포함되고, MAX 값을 추출하는 컬럼을 제외한 모든 컬럼들이
EQUAL로 조회 될 때, 인덱스 스캔이 INDEX RANGE SCAN(MIN/MAX)로 수행되어, 적은 리소스를 사용하여 MIN/MAX 데이터를 추출할 수 있따.

그러나 MIN,MAX 함수를 수행하는 추출 컬럼에 변형이 가해지면 INDEX RANGE SCAN(MIN/MAX)로 수행되지 못하므로 성능 문제가
발생할 수 있다. 그러므로 MIN, MAX값을 추출하는 컬럼에 컬럼 변형이 가해지지 않도록 반드시 주의해야한다.

DROP TABLE T1 PURGE;

CREATE TABLE T1
(
  CUSTID NUMBER,
  CUSTNAME VARCHAR2(50)
);

BEGIN
  FOR I IN 1..100000
  LOOP
    IF (MOD(I,5)=0) THEN
      INSERT INTO T1 VALUES(I,'A');
    END IF;
    IF (MOD(I,5)=1) THEN
      INSERT INTO T1 VALUES(I,'B');
    END IF;
    IF (MOD(I,5)=2) THEN
      INSERT INTO T1 VALUES(I,'C');
    END IF;
    IF (MOD(I,5)=3) THEN
      INSERT INTO T1 VALUES(I,'D');
    END IF;
    IF (MOD(I,5)=4) THEN
      INSERT INTO T1 VALUES(I,'E');
    END IF;
  END LOOP;
  COMMIT;
END;

CREATE INDEX IDX01_T1_CUSTID ON T1(CUSTNAME, CUSTID);

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'BSHMAN'
                                ,TABNAME=>'T1'
                                ,ESTIMATE_PERCENT=>99
                                ,METHOD_OPT=>'FOR ALL INDEXED COLUMNS'
                                ,GRANULARITY=>'ALL'
                                ,CASCADE=>TRUE
                                ,NO_INVALIDATE=>FALSE);
END;


테스트[1] 컬럼 변형에 의한 비효율적인 MAX()추출

SELECT MAX(TO_CHAR(CUSTID)) AS CUSTID
 FROM T1
WHERE CUSTNAME='A'

Plan hash value: 3773181093

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     1 |     6 |    52   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                 |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX01_T1_CUSTID | 19971 |   117K|    52   (2)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUSTNAME"='A')


테스트[2] 컬럼 변형 없이 인덱스를 이용한 효율적인 MAX()추출

SELECT TO_CHAR(MAX(CUSTID)) AS CUSTID
 FROM T1
WHERE CUSTNAME='A'

Plan hash value: 3855659005

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                 |     1 |     6 |            |          |
|   2 |   FIRST ROW                  |                 | 19971 |   117K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IDX01_T1_CUSTID | 19971 |   117K|     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CUSTNAME"='A')


테스트[1]은 MAX값을 추출할 컬럼을 TO_CHART(CUSTID)로 컬럼 변형을 한 후에 MAX값을 추출하여 비효율 발생
테스트[2]은 MAX(CUSTID)을 수행한후 TO_CHAR함수로형 변환 처리하여 INDEX RANGE SCAN(MIN/MAX)로 처리하여 효율 발생


파티션 키 컬럼 변형은 모든 파티션을 읽는다


파티션 테이블을 조회할 떄 겪는 성능 문제 중에, 파티션 키 컬럼의 데이터 타입과 조회 변수 데이터타입이 달라서
발생하는 경우가 있다. 자주 목격 되지는 않지만 성능 문제가 발생한다면 DB서버 전체에 큰 부하를 줄 수 있으므로 주의해야 한다.

날짜(월) 컬럼을 기준으로 월별 파티션을 생성한 테이블이 있다고 가정한다.
데이터 특성상 보관주기가 길다면, 시간이 지날수록 파티션의 개수는 점점 많아 질 것이다.

많은 파티션들 중에서 특정 월 데이터에 대해 조회를 했지만, 의도와는 다르게 전체 파티션을 조회한다면 WHERE절 조건 중 파티션 키
컬럼의 형 변환이 발생하였는지 의심해야 한다. 왜냐하면, 파티션 키 컬럼이 변형되면, 전체 파티션을 조회해야 하기 때문이다.

하단에서 파티션 키 컬럼 변형으로 인한 성능문제 테스트 확인한다.

CREATE TABLE PART_T1
(
  ID NUMBER NOT NULL,
  MEM VARCHAR2(2) NOT NULL,
  REGDATE VARCHAR2(8) NOT NULL
) PARTITION BY RANGE(REGDATE)
(
  PARTITION PART_T1_201001 VALUES LESS THAN('20100201'),
  PARTITION PART_T1_201002 VALUES LESS THAN('20100301'),
  PARTITION PART_T1_201003 VALUES LESS THAN('20100401'),
  PARTITION PART_T1_201004 VALUES LESS THAN('20100501'),
  PARTITION PART_T1_201005 VALUES LESS THAN('20100601'),
  PARTITION PART_T1_201006 VALUES LESS THAN('20100701'),
  PARTITION PART_T1_201007 VALUES LESS THAN('20100801'),
  PARTITION PART_T1_201008 VALUES LESS THAN('20100901'),
  PARTITION PART_T1_201009 VALUES LESS THAN('20101001'),
  PARTITION PART_T1_2010010 VALUES LESS THAN('20101101'),
  PARTITION PART_T1_2010011 VALUES LESS THAN('20101201'),
  PARTITION PART_T1_2010012 VALUES LESS THAN('20110101')
);


INSERT INTO PART_T1 VALUES(1,'A1','20100101');
INSERT INTO PART_T1 VALUES(2,'B1','20100201');
INSERT INTO PART_T1 VALUES(3,'C1','20100301');
INSERT INTO PART_T1 VALUES(4,'D1','20100401');
INSERT INTO PART_T1 VALUES(5,'E1','20100501');
INSERT INTO PART_T1 VALUES(6,'A1','20100601');
INSERT INTO PART_T1 VALUES(7,'B1','20100701');
INSERT INTO PART_T1 VALUES(8,'C1','20100801');
INSERT INTO PART_T1 VALUES(9,'D1','20100901');
INSERT INTO PART_T1 VALUES(10,'E1','20101001');
INSERT INTO PART_T1 VALUES(11,'D1','20101101');
INSERT INTO PART_T1 VALUES(12,'E1','20101201');
--
INSERT INTO PART_T1 VALUES(101,'A1','20100101');
INSERT INTO PART_T1 VALUES(102,'B1','20100201');
INSERT INTO PART_T1 VALUES(103,'C1','20100301');
INSERT INTO PART_T1 VALUES(104,'D1','20100401');
INSERT INTO PART_T1 VALUES(105,'E1','20100501');
INSERT INTO PART_T1 VALUES(106,'A1','20100601');
INSERT INTO PART_T1 VALUES(107,'B1','20100701');
INSERT INTO PART_T1 VALUES(108,'C1','20100801');
INSERT INTO PART_T1 VALUES(109,'D1','20100901');
INSERT INTO PART_T1 VALUES(110,'E1','20101001');
INSERT INTO PART_T1 VALUES(111,'D1','20101101');
INSERT INTO PART_T1 VALUES(112,'E1','20101201');

COMMIT;


테스트[1] 컬럼 변형에 의한 비효율 파티션 테이블 조회

SELECT /*+ FULL(PART_T1)*/
 *
 FROM PART_T1
WHERE REGDATE BETWEEN TO_DATE('20101101','YYYYMMDD')
  AND TO_DATE('20101131','YYYYMMDD');     <--- 컬럼 데이터 타입과 조건 데이터 타입 불일치

  Plan hash value: 4125059826

------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     2 |    44 |    15   (0)| 00:00:01 |       |       |
|*  1 |  FILTER              |         |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ALL|         |     2 |    44 |    15   (0)| 00:00:01 |     1 |    12 |
|*  3 |    TABLE ACCESS FULL | PART_T1 |     2 |    44 |    15   (0)| 00:00:01 |     1 |    12 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('2010-11-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss')<=TO_DATE('20101131','YYYYMMDD'))
   3 - filter(INTERNAL_FUNCTION("REGDATE")>=TO_DATE('2010-11-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND INTERNAL_FUNCTION("REGDATE")<=TO_DATE('20101131','YYYYMMDD'))

PART_T1 테이블은 2010년 1월부터 12월까지 데이터를 월 단위 파티션으로 구성하여
총12개의 파티션을 가지고 있다. 테스트[1]의 SQL은 파티션 키 조건인 REGDATE 컬럼에 조회 조건을 부여해,
2010년 11월 파티션 테이블만 읽고 데이터를 추출하려고 하였으나, 플랜 결과를 보면 PARTITION RANGE ALL 오퍼레이션이 발생하여,
이유는 REGDATE컬럼은 VARCHAR2(8) 이나 WHERE 절 조회 시 TO_DATE 함수를 사용하여, 파티션 키컬럼과 조회 조건 데이터 타입이
서로 달라서 ORACLE 형 변환 우선순위에의해 내부적으로 형 변환이 발생하였다.
파티션 키 컬럼에 내부 형 변환이 이루어질 경우, 단일 파티션만 조회하지 못하고 전체 파티션을 읽어야 하기 때문에,
자칫하면 DB서버에 성능 문제를 큰 문제를 야기할 수 있으므로 각별히 주의 해야한다.


테스트[2] 컬럼 변형 없이 효율적인 파티션 테이블 조회

SELECT /*+ FULL(PART_T1)*/
 *
 FROM PART_T1
WHERE REGDATE BETWEEN '20101101' AND '20101131';

Plan hash value: 2410793023

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |         |     2 |    44 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|         |     2 |    44 |     3   (0)| 00:00:01 |    11 |    11 |
|*  2 |   TABLE ACCESS FULL    | PART_T1 |     2 |    44 |     3   (0)| 00:00:01 |    11 |    11 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("REGDATE"<='20101131')
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3819

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입