Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero 7.2.4
Document Number | TTUTI037
Overview
This is an example of specifying column names in hints during data retrieval to guide the Optimizer to select the most appropriate index.
Method
1-1. Creating Test Objects and Data
Run the following statements first to perform the INDEX hint usage example.
-- Create test table
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)
);
-- Insert 14 rows of data
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 indexes
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. Execute Without Using Hints
Check the execution plan of a query retrieving from the EMP table without using hints.
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)
Note
The IDX_EMP_01 index using the DEPTNO and JOB columns of the EMP table was used.
1-3. Using IDX_EMP_02 Index
This example guides the use of IDX_EMP_02 by specifying the DEPTNO and SAL columns of the EMP table.
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. Using IDX_EMP_03 Index
This example guides the use of IDX_EMP_03 by specifying the DEPTNO and HIREDATE columns of the EMP table.
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. Creating Test Objects and Data
Run the following statements first to perform the example comparing scan methods when using INDEX hints.
-- Create test table
DROP TABLE PROD;
CREATE TABLE PROD ( NO NUMBER NOT NULL, CD NUMBER NOT NULL, ID1 VARCHAR2(20) NOT NULL, ID2 VARCHAR2(20) NOT NULL );
-- Insert 1.2 million rows of data
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 uppercase random characters
, dbms_random.string('U',20) ID2
FROM DUAL CONNECT BY LEVEL<=1200000;
COMMIT;
-- Create indexes
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. Execute Without Using Hints
This is an example of querying only the columns that make up the composite index without using index hints.
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 | -------------------------------------------------------------------------------------------------------------------------------
Note
An INDEX FAST FULL SCAN was performed instead of a TABLE FULL SCAN.
2-3. Applying Index Hint on NO, CD Columns
This example guides the use of the IDX_PROD_NO_CD index by specifying the NO and CD columns of the PROD table.
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 | ------------------------------------------------------------------------------------------------------------------------
Note
IDX_PROD_NO_CD, a composite index on NO and CD, was FULL SCANNED, and the table was accessed to retrieve the ID1 column.
The result is sorted by NO and CD according to the index.
2-4. Applying Index Hint on CD, NO Columns
This example guides the use of the IDX_PROD_CD_NO_ID1 index by specifying the CD and NO columns of the PROD table.
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 | ---------------------------------------------------------------------------------------------------------------------------
Note
All columns NO, CD, and ID1 in the SELECT clause are included in IDX_PROD_CD_NO_ID1.
Data is retrieved by an INDEX FULL SCAN without table access.
2-5. Applying Index Hint on NO, CD Columns (Using CD Column in Condition)
This example retrieves data by performing a FULL SCAN of IDX_PROD_CD_NO_ID1 by applying the CD, NO index hint. The data is retrieved sorted by CD and NO due to the index. Although all columns NO, CD, ID1 in the SELECT clause are included in IDX_PROD_CD_NO_ID1, unlike example 2-2, data is retrieved by INDEX FULL SCAN, not INDEX FAST 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 | ---------------------------------------------------------------------------------------------------------------------------