INDEX_ASC, INDEX_DESC 힌트를 이용한 튜닝

INDEX_ASC, INDEX_DESC 힌트를 이용한 튜닝   MIX, MAX 값을 구할 때 힌트를 이용하는 경우 성능향성에 도움이 될 수 있다는 것으로 MIN(최소), MAX(최대)값을 구해야 하는 경우 INDEX_ASC, INDEX_DESC 힌트를 적절히 사용하면 성능 향상에 도움이 된다는 것을 확인해 보겠다.   예를 들어 EMP 테이블의 급여(SAL) 컬럼에 대해 인덱스가 걸려 있다고 했을 때 급여의 MAX값을 구하기 위해 인덱스 영역에 가서 맨 앞 또는 맨 뒤의 데이터 한 건을 가지고 오면 간단히 데이터를 추출하지만 반대쪽에서 데이터를 찾기 위해 한 건씩 전체 데이터를 스캔(SCAN)한다면 많은 시간이 걸릴 수 있다.   EMP 테이블의 SAL 컬럼에 인덱스가 걸려 있으며 10번 부서의 MAX(SAL)을 구한다고 가정을 하고 테스트를 해보자.   먼저 급여(SAL) 컬럼의 인덱스를 idx_emp_sal이라는 이름으로 생성한다.   SQL> create index idx_emp_sal on emp(sal) tablespace users_idx; 인덱스가 생성되었습니다.   힌트를 사용하지 않고 실행한 결과이다. 35초 정도 소요됨   SQL> set autotrace on; SQL> set timing on; SQL> select max(sal)   2  from emp   3  where deptno = 10;     MAX(SAL) ----------       8600   경  과: 00:00:35.09   Execution Plan ----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE   1    0  SORT (AGGREGATE)   2    1    TABLE ACCESS (FULL) OF 'EMP'     Statistics 0  recursive calls  0  db block gets  30957 consistent gets  30944 physical reads   0  redo size  383  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       결과를 확인해보면 EMP 테이블을 FULL SCAN하여 데이터를 SORT 후 MAX 값을 가지고 오는 것을 실행계획을 통해 볼 수 있다.   이번에는 힌트를 사용하여 결과를 확인한다. 쿼리를 실행하자 금방 결과가 출력됨을 확인 할 수 있다. (실행 시간이 채 1초도 걸리지 않는다.)   SQL> set timing on SQL> set autotrace on SQL> select /*+ index_desc(emp idx_emp_sal) */ max(sal)   2  from emp   3  where deptno = 10   4    and sal > 0   5    and  rownum = 1;     MAX(SAL) ----------       8600   경  과: 00:00:00.03   Execution Plan ----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=26)   1    0  SORT (AGGREGATE)   2    1    COUNT (STOPKEY)   3    2      TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=10 Card=           1273 Bytes=33098)     4    3        INDEX (RANGE SCAN DESCENDING) OF 'IDX_EMP_SAL' (NON           -UNIQUE) (Cost=2 Card=22914)     Statistics 0  recursive calls  0  db block gets  4 consistent gets  4 physical reads   0  redo size  383  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        consistent gets 와 physical reads가 힌트를 사용한 경우와 사용하지 않은 경우에 많은 차이가 남을 알 수 있다. 결국 전체 스캔(Scan)하여 많은 블록(Block)을 읽어 들이는지 아닌지의 차이이며 이 차이가 곧 응답 시간의 차이다.   오라클 데이터베이스가 CBO(비용기반 최적기, Cost Based Optimizer)로 운영되어 스스로 비용을 계산하여 적절한 실행계획을 만든다고 하지만 위 예문처럼 MAX 값을 구하기 위해서 SAL 컬럼의 인덱스에서 역순으로 맨 위에 나타나는 데이터가 MAX 값이라는 것을 찾기는 쉽지 않아 보인다. 이러한 경우 쿼리를 만든 당사자가 직접 오라클 최적기(Oracle Optimizer)에게 데이터를 추출하기 위한 경로를 힌트(Hint)를 통해 한다면 성능 향상을 보장할 수 있다. 위 예문과 반대로 MIN 값을 구하기 위해서는 INDEX_ASC를 사용하면 된다는 사실은 쉽게 알 수 있으므로  생략 한다.