소속 회사가 제공하는 서비스 중 대다수는 금융 회사가 제출한(매일 혹은 매월) 여러 금융 상품의 정보를 가공한 것이다. 따라서 특정 일자의 여러 가지 상품별 정보를 보여주거나 특정 상품의 기간별 정보를 보여주는 화면이 매우 많아 대부분의 서비스 목적 테이블에는 다음과 같은 컬럼 및 인덱스가 존재한다.

만약 위 테이블 및 테이블의 인덱스를 활용하여 다음과 같은 업무 요건의 SQL을 작성하려면 어떠한 인덱스를 선택해야 할까?

예상한 것처럼 복합 인덱스 2번을 선택해야 한다. 복합 인덱스 2번은 상품코드가 선행 컬럼이므로 ‘123456’의 위치를 먼저 빠르게 탐색이 가능하다. 그리고 해당 코드에 대한 기준일자 또한 정렬되어 있을 것이므로 2016년 9월 1일 이전의 값에 빠르게 접근할 수 있다

반면 복합 인덱스 1을 선택했을 경우는 선행 컬럼이 기준일자 이므로 2016년 9월 1일부터 해당 코드가 있는지 검색하고 없을 경우 2016년 8월 31일 > 2016년 8월 30일 > 2016년 8월 29일 순으로 찾을 때까지 검색하게 된다.

이러한 이유로 참고 4-12에서 보는 것처럼 2016년 9월 1일에 결과가 찾아진 123456의 상품에 대해선 인덱스를 올바르게 선택했을 때와 유사한 성능(consistent gets 8) 을 보이지만 2016년 1월 10일에서야 결과를 찾은 789ABC의 경우는 많은 성능 차이(consistent gets 17225)를 보이게 된다.

만약 결과가 없는(NULL) 상품코드의 경우는 복합 인덱스 2의 경우 없음을 바로 인지하고 NULL을 리턴하겠지만 복합 인덱스 1은 전체 날짜를 모두 탐색하고 나서야 NULL임을 인지할 수 있으므로 심각한 성능저하가 발생하게 된다.

이처럼 동일한 쿼리가 조건에 따라 성능의 편차가 큰 SQL들은 잘못되었다는 점을 인식하기 힘들어 원인 파악이 어려운 특징이 있다. 따라서 소속 회사의 테이블과 같이 유사한 그러나 그 목적과 결과는 정말 상이한 복합 인덱스를 보유하고 있다면 인덱스 선택 시 신중을 기해야 할 것이다.

잘못된 인덱스 선택 - [기준일자, 상품코드]

SELECT /*+ INDEX_DESC( TBL IX01_수수료 ) */ 기준일자
FROM 수수료정보테이블 TBL
WHERE 기준일자 <= '20160901'
AND 상품코드 = '123456'
AND ROWNUM = 1;

올바른 인덱스 선택 - [상품코드, 기준일자]

SELECT /*+ INDEX_DESC( TBL IX02_수수료 ) */ 기준일자
FROM 수수료정보테이블 TBL
WHERE 기준일자 <= '20160901'
AND 상품코드 = '123456'
AND ROWNUM = 1;

1) 상품코드가 123456일 경우 - 결과 2016년 9월 1일

Execution Plan(올바른 인덱스 선택)
---------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:6, %%CPU:0, Rows:1)
2 INDEX (RANGE SCAN) DESCENDING: IX02_수수료 (Cost:6, %%CPU:0, Rows:306)

NAME VALUE
------------------- ----------
db block gets 0
consistent gets 8
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0


Execution Plan(잘못된 인덱스 선택)
---------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:803, %%CPU:99, Rows:1)
2 PARTITION RANGE (SUBSET PART) (Cost:803, %%CPU:99, Rows:306) PS:1, PE:15)
3 FILTER (Cost:803, %%CPU:99, Rows:306)
4 INDEX (RANGE SCAN) DESCENDING: IX01_수수료 (Cost:3, %%CPU:0, Rows:48532550) 

NAME VALUE
----------------------- ----------
db block gets 0
consistent gets 8
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0

2) 상품코드가 789ABC인 경우 - 결과 2016년 1월 1일

Execution Plan(올바른 인덱스 선택)
---------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:11, %%CPU:0, Rows:1)
2 INDEX (RANGE SCAN) DESCENDING: IX02_수수료 (Cost:11, %%CPU:0, Rows:1163) 

NAME VALUE
------------------- ----------
db block gets 0
consistent gets 8
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0

Execution Plan(잘못된 인덱스 선택)
--------------------------------------
1 COUNT (STOP NODE) (STOP LIMIT 2) (Cost:803, %%CPU:99, Rows:1)
2 PARTITION RANGE (SUBSET PART) (Cost:803, %%CPU:99, Rows:1163) (PS:1, PE:15)
3 FILTER (Cost:803, %%CPU:99, Rows:1163)
4 INDEX (RANGE SCAN) DESCENDING: IX01_수수료 (Cost:3, %%CPU:0, Rows:48532550) 

NAME VALUE
--------------- ----------
db block gets 0
consistent gets 17225
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 0

비효율 4)에 대한 얘기를 끝내기 전에 특정 컬럼의 MAX를 추출할 때 MAX 함수를 사용하는 것과 위 예시와 같이 INDEX_DESC HINT를 사용(MIN의 경우는 MIN함수 혹은 INDEX_ASC HINT)하는 것이 성능상 차이가 있을지 확인해보자.

4-13 결과를 보면 두 방법 모두 큰 성능 차이^주4) 없이 MAX 값이 추출되므로 무리없이 사용이 가능한 것으로 확인된다. 다만 인덱스 힌트를 사용하는 경우는 비효율4)와 같은 일이 발생하지 않도록 반드시 알맞은 인덱스를 선택한 것이 맞는지 확인해 볼 필요가 있다.

|주4| MAX 함수 PLAN을 보면 SORT AGGR(집합 함수(COUNT, MAX, MIN등) 처리) OPERATION에 메모리를 67K를 사용한 것으로 나타나는데, 위 쿼리는 MAX 값이 인덱스 레코드 자체에 포함되어 있으므로 SORT AGGR OPERATION이 동작할 필요가 없기 때문에, 실제로는 사용되지 않는 것으로 티베로 연구소와 확인했으며 향후에는 메모리 할당도 하지 않도록 개선될 예정이다.

• [참고 4-13] MAX 추출

•  -- TEST TABLE 및 DATA 생성
  CREATE TABLE TIBEROTEST.MAXTEST (PROCESS_DT VARCHAR(8) NOT NULL);
  CREATE INDEX TIBEROTEST.MAXTEST_IDX1 ON TIBEROTEST.MAXTEST(PROCESS_DT);
  
  INSERT INTO TIBEROTEST.MAXTEST 
  SELECT LEVEL 
    FROM DUAL CONNECT BY LEVEL <= 100000; 
  COMMIT;
  
  alter session set GATHER_SQL_PLAN_STAT='Y';
  
  --1) MAX 함수를 통한 MAX VALUE 추출
  
  SELECT --MAXTEST
         MAX (PROCESS_DT) FROM MAXTEST;
  
  
  --2) INDEX_DESC를 통한 MAX VALUE 추출
  
  SELECT --MAXTESTIDX
         /*+ INDEX_DESC(MAXTEST MAXTEST_IDX1) */ PROCESS_DT 
    FROM TIBEROTEST.MAXTEST WHERE ROWNUM < 2;
  
  
  --3) 실제 수행 PLAN 비교
  
  select sql_id 
    from v$sql where sql_text like '%SELECT --MAXTEST%';
    
  (g13kdaunvrf3p) >> select * from table(dbms_xplan.display_
  cursor('g13kdaunvrf3p', 'ALL'));
  
  
  select sql_id 
    from v$sql where sql_text like '%SELECT --MAXTESTIDX%';
  
  (2fmzh2nhk6fpz) >> select * from table(dbms_xplan.display_
  cursor('2fmzh2nhk6fpz', 'ALL')); 
  
  

• 가. MAX 함수 사용
• 
• 나. INDEX_DESC 사용
•