跟着官方文档学HINT（三）优化器特性

SQL> show parameter optimizer_features_enable    NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------optimizer_features_enable            string      11.2.0.3OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 |  9.0.0 | 9.0.1 | 9.2.0 | 9.2.0.8 |  10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5 |   11.1.0.6 | 11.1.0.7 | 11.2.0.1 |11.2.0.2  |11.2.0.3 |11.2.0.4 }SESSION  SYSTEM 级别可改也可以使用本文推荐的提示 optimizer_features_enable  顾名思义就是在某条sql语句中声明优化器使用的版本下面做一个实验看一下SQL> create table test_skip_scan  2  as  3  select mod(level,10) low_dist ,level uniq  4  from dual  5  connect by level <=1000000;Table created.SQL> create index IDX_DAO_SKIP on TEST_SKIP_SCAN (LOW_DIST, UNIQ);Index created.SQL> ANALYZE TABLE  test_skip_scan  COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;Table analyzed. SQL> select * from test_skip_scan where uniq=888 ;Execution Plan----------------------------------------------------------Plan hash value: 1582535247---------------------------------------------------------------------------------| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------|   0 | SELECT STATEMENT |              |     1 |     6 |    12   (0)| 00:00:01 ||*  1 |  INDEX SKIP SCAN | IDX_DAO_SKIP |     1 |     6 |    12   (0)| 00:00:01 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("UNIQ"=888)       filter("UNIQ"=888)Note-----   - dynamic sampling used for this statement (level=2)Statistics----------------------------------------------------------          6  recursive calls          0  db block gets        100  consistent gets         19  physical reads          0  redo size        596  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processedselect /*+ optimizer_features_enable('8.0.0') */  * from test_skip_scan where uniq=888 ;Execution Plan----------------------------------------------------------Plan hash value: 2650644146--------------------------------------------------------------------| Id  | Operation         | Name           | Rows  | Bytes | Cost  |--------------------------------------------------------------------|   0 | SELECT STATEMENT  |                |     1 |    26 |   277 ||*  1 |  TABLE ACCESS FULL| TEST_SKIP_SCAN |     1 |    26 |   277 |--------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("UNIQ"=888)Note-----   - cpu costing is off (consider enabling it)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets       1788  consistent gets          0  physical reads          0  redo size        596  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed由于使用提示指定优化器版本为8.0.0 ，而index skip scan 又是Oracle9i 引入。所以生成的执行计划必然不会使用，执行计划变成了全表扫描。