SQL> alter system flush buffer_cache;

System altered.

SQL> set autotrace traceonly;
  ----再次查看之前sql的执行计划----
SQL> select * from jack where object_id>1000 and object_id<2000;

9880 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2860868395

----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |          |  9657 |  914K|  162    (0)| 00:00:02 |
|  1 |  TABLE ACCESS BY INDEX ROWID| JACK    |  9657 |  914K|  162    (0)| 00:00:02 |
|*  2 |  INDEX RANGE SCAN        | JACK_IND |  9657 |      |    24    (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

  2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<2000)

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
    1457  consistent gets
    151  physical reads
      0  redo size
  988947  bytes sent via SQL*Net to client
    7657  bytes received via SQL*Net from client
    660  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    9880  rows processed
  ----注意这里的cost已经降到了162，性能提升还是非常明显。

 五、小结

    通过以上说明和测试，可以看到clustering factor也是索引健康的一个重要判断的标准。其值越低越好。它会影响CBO选择正确的执行计划。但是注意一点，clustering factor总是趋势与不断恶化的。