인덱스와 관련된 힌트를 사용하게 되면 최적기(Oracle Optimizer)는 기술된 인덱스를 써야 한다는
것을 알게 되며 이 경우 전체 Table Full Scan을 하지 않게 된다. 물론 사용되는 인덱스에 대해 COST(비용)는 계산하지 않고
존재하는 인덱스라면 사용 하게 되는 것이다.
실습테이블인 SCOTT계정의 EMP 테이블을 통해 실험을 해 보자.
(EMP 테이블에서 20번 부서의 입사일이 '81/01/31', 성명이 ‘SMITH’로 시작되며 급여가 2400보다 큰 데이터를 추출한다고 가정 하자)
먼저 실습을 위해 hiredate(입사일), ename(성명), sal(급여) 컬럼에 대해 인덱스를 생성 한다.
SQL> create index idx_emp_hiredate on emp(hiredate);
SQL> create index idx_emp_ename on emp(ename);
SQL> create index idx_emp_sal on emp(sal);
먼저 인덱스 힌트를 사용하지 않고 SQL문을 실행한다.
(1분 19초 소요)
SQL> set timing on
SQL> set autotrace on
SQL> select
2 ename, sal, hiredate
3 from emp
4 where hiredate = '81/01/31'
5 and sal > 2400
6 and deptno = 20
7 and ename like 'SMITH%';
ENAME SAL HIREDATE
---------------------- ---------- --------------------------------
SMITH1 2600 81/01/31
SMITH2 2600 81/01/31
SMITH3 2600 81/01/31
…….
SMITH10 2600 81/01/31
10 개의 행이 선택되었습니다.
경 과: 00:01:19.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'IDX_EMP_HIREDATE' (NON-UNIQUE)
Statistics
0 recursive calls 0 db block gets 20651
consistent gets 18488
physical reads
0 redo size 1014 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) 10 rows processed
예상 외로 느린 응답이 나왔는데 현재 Oracle 서버의 Optimizer Mode가 CHOOSE이므로 통계정보가 있으면 CBO로, 없으면 RBO로 동작을 하므로 CBO로 동작되어 Optimizer 스스로 IDX_EMP_HIREDATE 인덱스를 통해 데이터를 추출하는 것이 낫다고 판단을 하여 위와 같은 결과가 나왔으며 아래처럼 명시적으로 힌트를 걸어주면 Optimizer는 무조건 IDX_EMP_ENAME 인덱스를 사용하여 데이터를 추출한다.
이번에는 힌트를 사용하여 명시적으로 ename 컬럼에 걸려 있는 idx_emp_ename 인덱스를 경유하도록 지정(1초 정도 소요)
SQL> select /*+ index(emp idx_emp_ename) */
2 ename, sal, hiredate
3 from emp
4 where hiredate = '81/01/31'
5 and sal > 2400
6 and deptno = 20
7 and ename like 'SMITH%';
ENAME SAL HIREDATE
---------------------- ---------- --------------------------------
SMITH1 2600 81/01/31
SMITH2 2600 81/01/31
SMITH3 2600 81/01/31
…….
SMITH10 2600 81/01/31
10 개의 행이 선택되었습니다.
경 과: 00:00:01.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=62)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=10 Card=1 By
tes=62)
2 1 INDEX (RANGE SCAN) OF 'IDX_EMP_ENAME' (NON-UNIQUE) (Cos
t=2 Card=22914)
Statistics
0 recursive calls 0 db block gets 1148
consistent gets 202
physical reads
0 redo size 1014 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) 10 rows processed
consistent gets와 plysical reads에서 많은 차이가 있음을 알 수 있다. 즉 임의의 한 블록에 대해 요구된 지속적인 읽기 횟수 및 디스크로부터 읽어 들인 데이터 블록의 총 개수에서 많은 차이가 나타난다.
입사일(hiredate) 컬럼에 걸려 있는 인덱스(idx_emp_hiredate)보다 성명(ename) 컬럼에 걸려있는 idx_emp_ename 인덱스를 경유하는 경우 쿼리 수행 성능에 상당한 도움이 되는 사실을 사용자는 오라클 최적기(Oracle Optimizer)는 정확히 알아내기 어렵다. 만약 인덱스를 생성한 후 Analyze Table 명령으로 통계정보를 생성했다면 위 실험 결과와 다르게 나올 수 있다. 이렇게 오라클 최적기(Oracle Optimizer)가 스스로 판단한 인덱스를 경유하는 경우 보다 쿼리를 만든 당사자(개발자)가 의미 있게 지정하는 인덱스가 수행 성능에 훨씬 도움이 될 수 있다. 이렇듯 명시적으로 인덱스를 지정할 때 사용되는 힌트가 INDEX 이다.
실습테이블인 SCOTT계정의 EMP 테이블을 통해 실험을 해 보자.
(EMP 테이블에서 20번 부서의 입사일이 '81/01/31', 성명이 ‘SMITH’로 시작되며 급여가 2400보다 큰 데이터를 추출한다고 가정 하자)
먼저 실습을 위해 hiredate(입사일), ename(성명), sal(급여) 컬럼에 대해 인덱스를 생성 한다.
SQL> create index idx_emp_hiredate on emp(hiredate);
SQL> create index idx_emp_ename on emp(ename);
SQL> create index idx_emp_sal on emp(sal);
먼저 인덱스 힌트를 사용하지 않고 SQL문을 실행한다.
(1분 19초 소요)
SQL> set timing on
SQL> set autotrace on
SQL> select
2 ename, sal, hiredate
3 from emp
4 where hiredate = '81/01/31'
5 and sal > 2400
6 and deptno = 20
7 and ename like 'SMITH%';
ENAME SAL HIREDATE
---------------------- ---------- --------------------------------
SMITH1 2600 81/01/31
SMITH2 2600 81/01/31
SMITH3 2600 81/01/31
…….
SMITH10 2600 81/01/31
10 개의 행이 선택되었습니다.
경 과: 00:01:19.02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'IDX_EMP_HIREDATE' (NON-UNIQUE)
Statistics
0 recursive calls 0 db block gets 20651
consistent gets 18488
physical reads
0 redo size 1014 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) 10 rows processed
예상 외로 느린 응답이 나왔는데 현재 Oracle 서버의 Optimizer Mode가 CHOOSE이므로 통계정보가 있으면 CBO로, 없으면 RBO로 동작을 하므로 CBO로 동작되어 Optimizer 스스로 IDX_EMP_HIREDATE 인덱스를 통해 데이터를 추출하는 것이 낫다고 판단을 하여 위와 같은 결과가 나왔으며 아래처럼 명시적으로 힌트를 걸어주면 Optimizer는 무조건 IDX_EMP_ENAME 인덱스를 사용하여 데이터를 추출한다.
이번에는 힌트를 사용하여 명시적으로 ename 컬럼에 걸려 있는 idx_emp_ename 인덱스를 경유하도록 지정(1초 정도 소요)
SQL> select /*+ index(emp idx_emp_ename) */
2 ename, sal, hiredate
3 from emp
4 where hiredate = '81/01/31'
5 and sal > 2400
6 and deptno = 20
7 and ename like 'SMITH%';
ENAME SAL HIREDATE
---------------------- ---------- --------------------------------
SMITH1 2600 81/01/31
SMITH2 2600 81/01/31
SMITH3 2600 81/01/31
…….
SMITH10 2600 81/01/31
10 개의 행이 선택되었습니다.
경 과: 00:00:01.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=62)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=10 Card=1 By
tes=62)
2 1 INDEX (RANGE SCAN) OF 'IDX_EMP_ENAME' (NON-UNIQUE) (Cos
t=2 Card=22914)
Statistics
0 recursive calls 0 db block gets 1148
consistent gets 202
physical reads
0 redo size 1014 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) 10 rows processed
consistent gets와 plysical reads에서 많은 차이가 있음을 알 수 있다. 즉 임의의 한 블록에 대해 요구된 지속적인 읽기 횟수 및 디스크로부터 읽어 들인 데이터 블록의 총 개수에서 많은 차이가 나타난다.
입사일(hiredate) 컬럼에 걸려 있는 인덱스(idx_emp_hiredate)보다 성명(ename) 컬럼에 걸려있는 idx_emp_ename 인덱스를 경유하는 경우 쿼리 수행 성능에 상당한 도움이 되는 사실을 사용자는 오라클 최적기(Oracle Optimizer)는 정확히 알아내기 어렵다. 만약 인덱스를 생성한 후 Analyze Table 명령으로 통계정보를 생성했다면 위 실험 결과와 다르게 나올 수 있다. 이렇게 오라클 최적기(Oracle Optimizer)가 스스로 판단한 인덱스를 경유하는 경우 보다 쿼리를 만든 당사자(개발자)가 의미 있게 지정하는 인덱스가 수행 성능에 훨씬 도움이 될 수 있다. 이렇듯 명시적으로 인덱스를 지정할 때 사용되는 힌트가 INDEX 이다.
댓글 없음:
댓글 쓰기