oracle full table scan,INDEX Skip Scan、INDEX FFS和Full Table Scan三者的选择

奇怪了，用10g的库，下面的例子就完全正确，难道这里9i和10g对于索引的选择不同么？

SQL> select * from full_tbl where object_name='TEST';

Execution Plan

----------------------------------------------------------

Plan hash value: 1293869270

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |     2 |    58 |    55   (2)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| FULL_TBL |     2 |    58 |    55   (2)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME"='TEST')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

230  consistent gets

0  physical reads

0  redo size

585  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ INDEX_SS(full_tbl idx_full_tbl) */ * from full_tbl where object_name='TEST';

Execution Plan

----------------------------------------------------------

Plan hash value: 299004628

---------------------------------------------------------------------------------

| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |              |     2 |    58 | 47495   (1)| 00:09:30|

|*  1 |  INDEX SKIP SCAN | IDX_FULL_TBL |     2 |    58 | 47495   (1)| 00:09:30|

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_NAME"='TEST')

filter("OBJECT_NAME"='TEST')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

270  consistent gets

0  physical reads

0  redo size

585  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ INDEX_FFS(full_tbl idx_full_tbl) */ * from full_tbl where object_name='TEST';

Execution Plan

----------------------------------------------------------

Plan hash value: 90102921

-------------------------------------------------------------------------------------

| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time|

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |              |     2 |    58 |    60   (0)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| IDX_FULL_TBL |     2 |    58 |    60   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_NAME"='TEST')

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

275  consistent gets

0  physical reads

0  redo size

585  bytes sent via SQL*Net to client

492  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed