문서유형ㅣ기술정보
분야ㅣ튜닝
적용제품버전ㅣTibero 7.2.4
문서번호ㅣTTUTI037
개요
데이터 조회 시 힌트에 컬럼명을 명시하여 Optimizer가 가장 적절한 Index를 선택하도록 유도하는 예제입니다.
방법
1-1. 테스트 오브젝트 및 데이터 생성
INDEX 힌트 사용 예제 수행을 위해 아래 구문을 먼저 수행합니다.
-- 테스트 테이블 생성
DROP TABLE EMP;
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
-- 데이터 14건 입력
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980/12/17', 'YYYY/MM/DD'), 800, null, 20);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981/02/20', 'YYYY/MM/DD'), 1600, 300, 30);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981/02/22', 'YYYY/MM/DD'), 1250, 500, 30);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981/04/02', 'YYYY/MM/DD'), 2975, null, 20);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981/09/28', 'YYYY/MM/DD'), 1250, 1400, 30);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981/05/01', 'YYYY/MM/DD'), 2850, null, 30);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981/06/09', 'YYYY/MM/DD'), 2450, null, 10);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1987/04/19', 'YYYY/MM/DD'), 3000, null, 20);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7839, 'KING', 'PRESIDENT', null, TO_DATE('1981/11/17', 'YYYY/MM/DD'), 5000, null, 10);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981/09/08', 'YYYY/MM/DD'), 1500, 0, 30);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1987/05/23', 'YYYY/MM/DD'), 1100, null, 20);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981/12/03', 'YYYY/MM/DD'), 950, null, 30);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981/12/03', 'YYYY/MM/DD'), 3000, null, 20);
insert into "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") values (7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982/01/23', 'YYYY/MM/DD'), 1300, null, 10);
-- 인덱스 생성
CREATE INDEX IDX_EMP_01 ON EMP (
DEPTNO ASC,
JOB ASC
);
CREATE INDEX IDX_EMP_02 ON EMP (
DEPTNO ASC,
SAL ASC
);
CREATE INDEX IDX_EMP_03 ON EMP (
DEPTNO ASC,
HIREDATE ASC
);
1-2. 힌트 없이 실행 하기
힌트 사용하지 않고 EMP 테이블을 조회하는 쿼리의 실행 계획을 확인합니다.
SQL>
SET PAGESIZE 100
COL HIREDATE FOR A10
COL JOB FOR A9
COL MGR FOR 9999
COL ENAME FOR A6
COL SAL FOR 9999
COL COMM FOR 9999
COL DEPTNO FOR 9999
SELECT * FROM EMP WHERE DEPTNO > 0;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTN
---------- ------ --------- ----- ---------- ----- ----- -----
7934 MILLER CLERK 7782 1982/01/23 1300 10
7782 CLARK MANAGER 7839 1981/06/09 2450 10
7839 KING PRESIDENT 1981/11/17 5000 10
7788 SCOTT ANALYST 7566 1987/04/19 3000 20
7902 FORD ANALYST 7566 1981/12/03 3000 20
7369 SMITH CLERK 7902 1980/12/17 800 20
7876 ADAMS CLERK 7788 1987/05/23 1100 20
7566 JONES MANAGER 7839 1981/04/02 2975 20
7900 JAMES CLERK 7698 1981/12/03 950 30
7698 BLAKE MANAGER 7839 1981/05/01 2850 30
7499 ALLEN SALESMAN 7698 1981/02/20 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 1981/09/08 1500 0 30
SQL> SET LINESIZE 180
SET PAGESIZE 0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : fx001vwgamt3s
CHILD NUMBER : 2256
HASH VALUE : 514450552
PLAN HASH VALUE: 4038989440
EXECUTIONS : 1
FETCHES : 1
LOADED AT : 2025/12/09 09:15:19
TOT ELAPSED TIME: 00:00:00.0003
AVG ELAPSED TIME: 00:00:00.0003
TOT BUFFER GETS: 8
AVG BUFFER GETS: 8
--------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
--------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS (ROWID) | EMP | 20 (0)| 238 | 14 |00:00:00.0000 | 1 | 1 |
| 2 | INDEX (RANGE SCAN) | IDX_EMP_01 | 2 (0)| 238 | 14 |00:00:00.0000 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 2
Predicate Information
--------------------------------------------------------------------------------------------------------------------
2 - access: ("EMP"."DEPTNO" > 0) (0.100)
참고
EMP 테이블의 DEPTNO, JOB컬럼을 사용하는 IDX_EMP_01 인덱스가 이용되었습니다.
1-3. IDX_EMP_02 인덱스 사용하기
EMP 테이블의 DEPTNO, SAL 컬럼을 명시하여 IDX_EMP_02 을 사용하도록 유도하는 예제입니다.
SQL> SELECT /*+INDEX(EMP(DEPTNO,SAL))*/ * FROM EMP WHERE DEPTNO > 0;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTN
---------- ------ --------- ----- ---------- ----- ----- -----
7934 MILLER CLERK 7782 1982/01/23 1300 10
7782 CLARK MANAGER 7839 1981/06/09 2450 10
7839 KING PRESIDENT 1981/11/17 5000 10
7369 SMITH CLERK 7902 1980/12/17 800 20
7876 ADAMS CLERK 7788 1987/05/23 1100 20
7566 JONES MANAGER 7839 1981/04/02 2975 20
7788 SCOTT ANALYST 7566 1987/04/19 3000 20
7902 FORD ANALYST 7566 1981/12/03 3000 20
7900 JAMES CLERK 7698 1981/12/03 950 30
7521 WARD SALESMAN 7698 1981/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 1981/09/08 1500 0 30
7499 ALLEN SALESMAN 7698 1981/02/20 1600 300 30
7698 BLAKE MANAGER 7839 1981/05/01 2850 30
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : 472vct594dfnr
CHILD NUMBER : 2257
HASH VALUE : 1380366999
PLAN HASH VALUE: 4063376851
EXECUTIONS : 1
FETCHES : 1
LOADED AT : 2025/12/09 09:20:32
TOT ELAPSED TIME: 00:00:00.0003
AVG ELAPSED TIME: 00:00:00.0003
TOT BUFFER GETS: 8
AVG BUFFER GETS: 8
--------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
--------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS (ROWID) | EMP | 20 (0)| 238 | 14 |00:00:00.0000 | 1 | 1 |
| 2 | INDEX (RANGE SCAN) | IDX_EMP_02 | 2 (0)| 238 | 14 |00:00:00.0000 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 2
Predicate Information
--------------------------------------------------------------------------------------------------------------------
2 - access: ("EMP"."DEPTNO" > 0) (0.100)
1-4. IDX_EMP_03 인덱스 사용하기
EMP 테이블의 DEPTNO, HIREDATE 컬럼을 명시하여 IDX_EMP_03 을 사용하도록 유도하는 예제입니다.
SQL> SELECT /*+INDEX(EMP(DEPTNO,HIREDATE))*/ * FROM EMP WHERE DEPTNO > 0;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTN
---------- ------ --------- ----- ---------- ----- ----- -----
7934 MILLER CLERK 7782 1982/01/23 1300 10
7782 CLARK MANAGER 7839 1981/06/09 2450 10
7839 KING PRESIDENT 1981/11/17 5000 10
7369 SMITH CLERK 7902 1980/12/17 800 20
7876 ADAMS CLERK 7788 1987/05/23 1100 20
7566 JONES MANAGER 7839 1981/04/02 2975 20
7788 SCOTT ANALYST 7566 1987/04/19 3000 20
7902 FORD ANALYST 7566 1981/12/03 3000 20
7900 JAMES CLERK 7698 1981/12/03 950 30
7521 WARD SALESMAN 7698 1981/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 1981/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 1981/09/08 1500 0 30
7499 ALLEN SALESMAN 7698 1981/02/20 1600 300 30
7698 BLAKE MANAGER 7839 1981/05/01 2850 30
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS'));
SQL ID : fx83vga76gpar
CHILD NUMBER : 2259
HASH VALUE : 2389169495
PLAN HASH VALUE: 128462301
EXECUTIONS : 1
FETCHES : 1
LOADED AT : 2025/12/09 09:44:32
TOT ELAPSED TIME: 00:00:00.0003
AVG ELAPSED TIME: 00:00:00.0003
TOT BUFFER GETS: 8
AVG BUFFER GETS: 8
--------------------------------------------------------------------------------------------------------------------
| ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts |
--------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS (ROWID) | EMP | 20 (0)| 238 | 14 |00:00:00.0000 | 1 | 1 |
| 2 | INDEX (RANGE SCAN) | IDX_EMP_03 | 2 (0)| 238 | 14 |00:00:00.0000 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------
LAST ELAPSED TIME: 00:00:00.0000
LAST BUFFER GET: 2
Predicate Information
--------------------------------------------------------------------------------------------------------------------
2 - access: ("EMP"."DEPTNO" > 0) (0.100)
2-1. 테스트 오브젝트 및 데이터 생성
INDEX 힌트 사용 시 스캔 방식을 비교하는 예제 수행을 위해 아래 구문을 먼저 수행합니다.
-- 테스트 테이블 생성
DROP TABLE PROD;
CREATE TABLE PROD ( NO NUMBER NOT NULL, CD NUMBER NOT NULL, ID1 VARCHAR2(20) NOT NULL, ID2 VARCHAR2(20) NOT NULL );
-- 데이터 120만건 입력
INSERT /*+ append */ INTO PROD
SELECT MOD(trunc(dbms_random.value(1,1000000)),90) * 4
-- MOD(ROWNUM-1, 80) * 5 NO
, ROWNUM - 1 CD
, dbms_random.string('U',20) ID1 --대문자 20자리 난수
, dbms_random.string('U',20) ID2
FROM DUAL CONNECT BY LEVEL<=1200000;
COMMIT;
-- 인덱스 생성
CREATE INDEX IDX_PROD_NO_CD ON PROD(NO,CD);
CREATE INDEX IDX_PROD_CD_NO_ID1 ON PROD(CD,NO,ID1);
CREATE INDEX IDX_PROD_ID1_NO_CD ON PROD(ID1,NO,CD);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TIBERO', TABNAME=>'PROD');2-2. 힌트 없이 실행
인덱스 힌트를 사용하지 않고 복합 인덱스에 구성된 컬럼만 조회하는 예제입니다.
SQL> SET ROWS OFF SQL> SELECT NO, CD, ID1 FROM PROD; 1200000 rows selected. SQL> SET ROWS ON SQL> SET PAGESIZE 0 SQL> SET LINESIZE 180 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS')); SQL ID : 5dbysnj4rsw76 CHILD NUMBER : 2384 HASH VALUE : 1232892134 PLAN HASH VALUE: 2777817376 EXECUTIONS : 1 FETCHES : 225 LOADED AT : 2025/12/09 13:24:49 TOT ELAPSED TIME: 00:00:00.3247 AVG ELAPSED TIME: 00:00:00.3247 TOT BUFFER GETS: 7226 AVG BUFFER GETS: 7226 ------------------------------------------------------------------------------------------------------------------------------- | ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts | ------------------------------------------------------------------------------------------------------------------------------- | 1 | COLUMN PROJECTION | | 2984 (.2)| 1200274 | 1200000 |00:00:00.0336 | 0 | 1 | | 2 | INDEX (FAST FULL SCAN) | IDX_PROD_CD_NO_ID1 | 2978 (0)| 1200274 | 1200000 |00:00:00.0136 | 7226 | 1 | -------------------------------------------------------------------------------------------------------------------------------
참고
TABLE을 FULL SCAN 하는 대신 INDEX FAST FULL SCAN이 수행되었습니다.
2-3. NO, CD 칼럼 인덱스 힌트 적용
PROD 테이블의 NO, CD 컬럼을 명시하여 IDX_PROD_NO_CD 인덱스를 사용하도록 유도하는 예제입니다.
SQL> SET ROWS OFF SQL> SELECT /*+ index(PROD (NO, CD)) */ NO, CD, ID1 FROM PROD; 1200000 rows selected. SQL> SET ROWS ON SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS')); SQL ID : 9nh2hgf02xu2n CHILD NUMBER : 234 HASH VALUE : 2150557780 PLAN HASH VALUE: 2557220150 EXECUTIONS : 3 FETCHES : 1805 LOADED AT : 2025/12/10 13:40:56 TOT ELAPSED TIME: 00:00:13.8463 AVG ELAPSED TIME: 00:00:04.6154 TOT BUFFER GETS: 1555514 AVG BUFFER GETS: 518505 ------------------------------------------------------------------------------------------------------------------------ | ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts | ------------------------------------------------------------------------------------------------------------------------ | 1 | TABLE ACCESS (ROWID) | PROD | 616K (.01)| 1200275 | 1200000 |00:00:00.6928 | 637K | 1 | | 2 | INDEX (FULL) | IDX_PROD_NO_CD | 3428 (0)| 1200275 | 1200000 |00:00:00.0054 | 3360 | 1 | ------------------------------------------------------------------------------------------------------------------------
참고
NO, CD 복합인덱스인 IDX_PROD_NO_CD 를 FULL SCAN하고 ID1컬럼 조회를 위해 테이블에 ACCESS
결과값은 인덱스에 의해 NO, CD로 정렬
2-4. CD, NO 컬럼 인덱스 힌트 적용
PROD 테이블의 CD, NO 컬럼을 명시하여 IDX_PROD_CD_NO_ID1 인덱스를 사용하도록 유도하는 예제입니다.
SQL> SET ROWS OFF SQL> SELECT /*+ index(PROD (CD, NO)) */ NO, CD, ID1 FROM PROD; 1200000 rows selected. SQL> SET ROWS ON SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS')); SQL ID : 6rxr7vyv7vvrx CHILD NUMBER : 240 HASH VALUE : 3061706493 PLAN HASH VALUE: 134119524 EXECUTIONS : 1 FETCHES : 866 LOADED AT : 2025/12/10 13:44:58 TOT ELAPSED TIME: 00:00:01.3099 AVG ELAPSED TIME: 00:00:01.3099 TOT BUFFER GETS: 6933 AVG BUFFER GETS: 6933 --------------------------------------------------------------------------------------------------------------------------- | ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts | --------------------------------------------------------------------------------------------------------------------------- | 1 | COLUMN PROJECTION | | 6749 (.09)| 1200275 | 1200000 |00:00:00.0328 | 0 | 1 | | 2 | INDEX (FULL) | IDX_PROD_CD_NO_ID1 | 6743 (0)| 1200275 | 1200000 |00:00:01.2589 | 6927 | 1 | ---------------------------------------------------------------------------------------------------------------------------
참고
SELECT 절의 NO, CD, ID1 컬럼은 모두 IDX_PROD_CD_NO_ID1 에 포함되어 있음
테이블 접근 없이 인덱스 FULL SCAN 으로 데이터를 가져옴
2-5. NO, CD 칼럼 인덱스 힌트 적용(조건절에서 CD 칼럼 사용)
CD, NO 인덱스 힌트를 적용하여 IDX_PROD_CD_NO_ID1를 FULL SCAN으로 데이터를 가져오는 예제입니다. 인덱스에 의해 CD, NO로 정렬되어 조회됩니다. SELECT 절의 NO, CD, ID1 컬럼은 모두 IDX_PROD_CD_NO_ID1 에 포함되어 있지만 2-2번 예제 수행과 달리 인덱스 FAST FULL SCAN이 아닌 인덱스 FULL SCAN으로 데이터를 가져옵니다.
SQL> SET ROWS OFF SQL> SELECT /*+ index(PROD (CD, NO)) */ NO, CD, ID1 FROM PROD; 1200000 rows selected. SQL> SET ROWS ON SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL BUFGETS')); SQL ID : 6rxr7vyv7vvrx CHILD NUMBER : 240 HASH VALUE : 3061706493 PLAN HASH VALUE: 134119524 EXECUTIONS : 1 FETCHES : 866 LOADED AT : 2025/12/10 13:44:58 TOT ELAPSED TIME: 00:00:01.3099 AVG ELAPSED TIME: 00:00:01.3099 TOT BUFFER GETS: 6933 AVG BUFFER GETS: 6933 --------------------------------------------------------------------------------------------------------------------------- | ID | Operation | Name | Cost (%CPU) | Cards | Rows | Elaps. Time | BUFGETS | Starts | --------------------------------------------------------------------------------------------------------------------------- | 1 | COLUMN PROJECTION | | 6749 (.09)| 1200275 | 1200000 |00:00:00.0328 | 0 | 1 | | 2 | INDEX (FULL) | IDX_PROD_CD_NO_ID1 | 6743 (0)| 1200275 | 1200000 |00:00:01.2589 | 6927 | 1 | ---------------------------------------------------------------------------------------------------------------------------