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

h2.부적절하게 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 절과 인덱스가 동일하게 정렬되어 있다면,
부분범위 처리로 유도할 수 있기 때문이다.

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()추출||
|{code:sql}
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')

Plan hash value: 3855659005

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)로 처리하여 효율 발생

   


||파티션 키 컬럼 변형은 모든 파티션을 읽는다||
|{code:sql}

파티션 테이블을 조회할 떄 겪는 성능 문제 중에, 파티션 키 컬럼의 데이터 타입과 조회 변수 데이터타입이 달라서
발생하는 경우가 있다. 자주 목격 되지는 않지만 성능 문제가 발생한다면 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;

Plan hash value: 4125059826

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] 컬럼 변형 없이 효율적인 파티션 테이블 조회 ||
|{code:sql}
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')