FULL 힌트를 이용한 튜닝

FULL 힌트를 이용한 튜닝
 
[실습환경]
 
실습 테이블 : EMP(3,670,016건), CUSTOMER(67,108,864건)
 인덱스 : EMP테이블에 대해 급여(SAL), 성명(ENAME), 입사일(HIREDATE)
컬럼에 대해 각각 인덱스 생성
 
각 컬럼에서 분포도를 확인 하기 위해 Unique한(중복되지 않은)
컬럼 건수는 아래와 같다.
(SAL, HIREDATE인 경우 중복되는 레코드가 많이 있음)
 
SQL> SELECT DISTINCT ENAME FROM EMP;    //3,502,248 건
SQL> SELECT DISTINCT SAL FROM EMP  ;    //126 건
SQL> SELECT DISTINCT HIREDATE FROM EMP  //211 건
 
        CUSTOMER 테이블에 대해 성별(SEX) 컬럼에 인덱스 생성
        (성별 컬럼에 대한 컬럼 값의 분포도는 50% 정도)
 
 
 
FULL 힌트를 이용한 튜닝
 
만약 성별처럼 "M" 아니면 "F"만 가지는 경우 분포도가 50%(둘 중 하나의 값을 가짐)이므로 인덱스를 사용하는 것보다 인덱스를 사용하지 않는 경우가 성능에 효율적일 수 있다. 이러한 경우 인덱스를 사용하지 않도록 하기 위해 테이블을 FULL SCAN 하도록 사용 하는 힌트가 FULL 구문 이다.
 
실습을 위해 customer라는 테이블을 생성하는데 성별(sex) 컬럼은 남자인 경우 ‘M’, 여자인  경우 ‘F’라는 값을 가진다고 가정 한다.
 
SQL> set timing on
SQL> set autotrace on
SQL> create table customer (
  2    name varchar2(20),
  3    addr varchar2(50),
  4    sex  varchar2(1)
  5  );
 
테이블이 생성되었습니다.
 
성능 차이를 확인하기 위해 데이터는 67,108,864 건 정도 입력했다.
 
SQL>  select count(*) from customer;
 
  COUNT(*)
----------
67108864
 
먼저 성별(sex) 컬럼에 인덱스를 생성한다 (인덱스를 경유하는 경우와 경유하지 않고  FULL SCAN하는 경우의 차이를 비교하기 위해)
 
SQL>create index idx_customer_sex on customer(sex) tablespace users_idx;
 
customer 테이블에서 성별이 남자인 경우는 현재 50% 정도이며 건수를 알고자 한다.
 
먼저 다음과 같이 힌트를 기술하지 않은 쿼리를 실행 한다.
(2분 30초 정도 소요됨)
 
SQL> set autotrace on
SQL> set timing on
SQL> select count(*) from customer where sex = 'M';
 
  COUNT(*)
----------
  33554432
 
경  과: 00:02:30.02
 
Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE
  1    0  SORT (AGGREGATE)
  2    1    INDEX (RANGE SCAN) OF 'IDX_CUSTOMER_SEX' (NON-UNIQUE)
 
Statistics
0  recursive calls 0  db block gets 60790
consistent gets 60790
physical reads
0  redo size 386  bytes sent via SQL*Net to client 503  bytes received via SQL*Net from client 2  SQL*Net roundtrips to/from client
0  sorts (memory) 0  sorts (disk) 1  rows processed 

     
 
이번에는 /*+ FULL(customer) */ 힌트를 사용하여 쿼리를 실행했다.
(1분 4초 정도 소요됨)
 
SQL> select /*+ FULL(customer) */  count(*) from customer where sex = 'M';
 
  COUNT(*)
----------
  33554432
 
경  과: 00:01:04.06
 
Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)
  1    0  SORT (AGGREGATE)
  2    1    TABLE ACCESS (FULL) OF 'CUSTOMER' (Cost=2 Card=1 Bytes=2
          )
 
Statistics
0  recursive calls 0  db block gets 329391
consistent gets 329367
physical reads
0  redo size 386  bytes sent via SQL*Net to client 503  bytes received via SQL*Net from client 2  SQL*Net roundtrips to/from client
0  sorts (memory) 0  sorts (disk) 1  rows processed 

 
 

 
위 결과는 이전의 결과와 다른 부분이 있다. FULL 힌트(Hint)를 사용한 SQL 구문이 오히려 consistent gets와 physical reads의 수치가 높음을 알 수 있다. 그런데 오히려 응답 시간(Reponse Time)은 FULL 힌트(Hint)를 사용하여 전체 테이블 스캔(Scan) 했을 때가 1분4초, 힌트(Hint)사용하지 않고 SEX 컬럼의 인덱스를 이용한 경우가 2분30초 정도 수행되었다. 이러한 이유는 인덱스 컬럼에 대한 SCAN은 기본적으로 단일 블록 스캔을 하므로 힌트를 사용하지 않고 SEX 컬럼의 인덱스를 이용한 경우 읽어 들인 블록은 적지만 수행 시간은 더 걸린 것이다.
 
인덱스가 필요한 경우 컬럼의 분포도가 10% 전 후로 되는 컬럼에 대해서 의미가 있고 위 예에서처럼 분포도가 50% 정도 되는 컬럼은 인덱스를 만들어 검색을 하는 것은 성능에 악 영향을 준다고 할 수 있다. 무조건 인덱스를 사용하는 것만이 좋다고 할 수 있는 것이 아니다.