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를 사용하면 된다는 사실은 쉽게 알 수 있으므로 생략 한다.
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를 사용하면 된다는 사실은 쉽게 알 수 있으므로 생략 한다.
댓글 없음:
댓글 쓰기