oracle11g 怎么没有Index Skip Scan

[光翟]

oracle11g 怎么没有Index Skip Scan 是不是默认没了，有参数？？？

[Wentasy]

什么是Index Skip Scan ?

[linwaterbin]

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(10)
 SEX                                                CHAR(2)

SQL> create index idx_t on t (sex,id);

Index created.

SQL> analyze index idx_t compute statistics;

Index analyzed.

SQL> set autot trace exp
SQL> select /*+ index_ss(t) */ id from t where id=8;

Execution Plan
----------------------------------------------------------
Plan hash value: 1727616455

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    13 |     4   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IDX_T |     1 |    13 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - access("ID"=8)
       filter("ID"=8)

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

[光翟]

根据楼上的信息怀疑有可能是oracle小版本有bug 经过测试：11.2.0.2.0版本 无法使用跳跃索引， 11.2.0.3.0就可以了。