Hints and Parameters

Index Range Scan 을 제어하는 전통적인 Hint 들은 다음과 같다.

• INDEX : Index 사용 여부를 지정한다. Index 이름을 지정하지 않는 경우에는 Optimizer 가 가장 적당한 Index 를 선택하도록 한다.
• INDEX_ASC : Index 를 오름 차순(Ascedning)으로 방문하도록 지정한다.
• INDEX_DESC : Index 를 내림 차순(Descending)으로 방문하도록 지정한다.

Oracle 10g 부터 INDEX Hint 를 지정하는 새로운 Naming Convention 이 소개되었다.

• 전통적인 INDEX Hint
  • select /*+ index(t1 t1_n1) */ ...

• 반면 Oracle 10g 부터는 Index 이름이 아닌 Column 이름을 지정하는 것이 가능해졌다.
  • select /*+ index(t1 t1(c1)) */ ....
  • select /*+ index(t1 t1(c1,c2)) */ ...

SYS>@gather t2
PL/SQL procedure successfully completed.

SYS>select /*+ gather_plan_statistics index(t2 t2(c1)) */ count(c1) from t2 where c1 > 9950 ;
 COUNT(C1)
----------
        50

SYS>@stat
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID  0dnp8xgfnb9u9, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t2 t2(c1)) */ count(c1) from t2 where c1 > 9950

Plan hash value: 622415417

--------------------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE   |       |      1 |      1 |            |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T2_N1 |      1 |     50 |     2   (0)|     50 |00:00:00.01 |       2 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------


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

   2 - access("C1">9950)

Note
-----
   - dynamic sampling used for this statement


22 rows selected.

SYS>@check_table_and_indexes.sql sys t2

TABLE_NAME                ROW_S     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN SAMPLE_SIZE ANA
-------------------- ---------- ---------- ------------ ----------- ----------- -----------------
T2                        10000         20            0           7      10,000 20090308 16:41:40


INDEX_NAME                ROW_S     BLEVEL     LEAF_B    DIS_KEY KEYPERLEAF CLUSETERING SAMPLE_SIZE ANA
-------------------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- -----------------
T2_N1                     10000          1         19      10000          1          18      10,000 20090308 16:41:40


C_NAME               DATA_TYPE   D_L NULL NUM_DISTINCT  DEN    NUM_NULLS NUM_BUC ANA                SAMPLE_SIZE USER_STATS HIS
-------------------- ---------- ---- ---- ------------ ---- ------------ ------- ----------------- ------------ ---------- ---------------
C1                   NUMBER       22 Y          10,000    0            0       1 20090308 16:41:40       10,000 NO         NONE
C2                   NUMBER       22 Y          10,000    0            0       1 20090308 16:41:40       10,000 NO         NONE

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALU
------------------------------ --------------- --------------------
C1                                           1
C1                                           0
C2                                           1
C2                                           0


SYS>show parameters OPTIMIZER_DYNAMIC_SAMPLING

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2

• Oracle 10.2.0.3 부터는 Index Range Scan 을 "직접" 지정할 수 있는 Hint 가 추가 되었다.
• INDEX_RS, INDEX_RS_ASC, INDEX_RS_DESC Hint 가 그것이다.
• 이런 Hint 가 추가된 이유는 INDEX Hint 의 애매 모호성 때문이다.
• ==> 구체적으로 Index Range Scan 을 타도록 확실히