Syntax

/*+ index(table_name index_name */

10g부터 사용

/*+ index(table_name table_name(column) */
/*+ index(table_name) */

예제)

CREATE TABLE T1 AS 
select ROWNUM C1,10000 - MOD(ROWNUM,100) C2 from DUAL
CONNECT BY LEVEL <= 10000;

create index t1_n1 on t1(c1);
create index t1_n2 on t1(c1, c2);
create index t1_n3 on t1(c2);

exec dbms_stats.gather_table_stats ('HYUN','t1',CASCADE=>TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');

기본적으로 사용하던 방법

select /*+ index(t1 t1_n1) */ *
  from t1
 where c1 = 1 and c2 = 9999;


-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"=9999)
   2 - access("C1"=1)

테이블에 컬럼을 직접 지정하여 사용하는 방법 (10g)

• 힌트에 하나의 인덱스 컬럼 지정

select /*+ index(t1 t1(c1)) */ *
  from t1
 where c1 = 1 and c2 = 9999;


-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"=9999)
   2 - access("C1"=1)   

테이블에 컬럼을 직접 지정하여 사용하는 방법 (10g)

• 힌트에 두개의 인덱스 컬럼 지정

  

select /*+ index(t1 t1(c1 c2)) */ *
  from t1
 where c1 = 1 and c2 = 9999;

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     7 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T1_N2 |     1 |     7 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=1 AND "C2"=9999)

인덱스명을 주지 않고 테이블 명만으로 인덱스 스캔

• 인덱스를 태우지 않게 하기 위해 인덱스 컬럼 가공

select  *
  from t1
 where c1||'' = 1 and c2 = 9999;


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     7 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"=9999 AND TO_NUMBER(TO_CHAR("C1")||'')=1)
   
   

select /*+ index(t1) */  *
  from t1
 where c1||'' = 1 and c2 = 9999;


-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     7 |    19   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |     7 |    19   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_N3 |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(TO_CHAR("C1")||'')=1)
   2 - access("C2"=9999)