SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS size
------------ ------------------ ---------- ---------- ---------
JACK_IND INDEX 1664 28 13M
----在没有收集相关的统计信息之前，查看一下index clustering factor----
SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
--------------- ----------------- ----------
JACK_IND 725460 725460
----简单的收集一下统计信息----
SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);
PL/SQL procedure successfully completed.
----再次查看index clustering factor----
SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';
INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
-------------- ----------------- ----------
JACK_IND 725460 725460 ----显然统计信息收集前和后，clustering factor值不变，说在创建索引的时候，会收集表中的数据真正的行数。并且这里的clustering factor等num_rows，也说明表的clustering factor是无序的。
----查看一个确定值，然后查看执行计划----
SQL> explain plan for select * from jack where object_id=1501;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2860868395
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 970 | 13 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID| JACK | 10 | 970 | 13 (0)| 00
|* 2 | INDEX RANGE SCAN | JACK_IND | 10 | | 3 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - access("OBJECT_ID"=1501)
14 rows selected. ----在这里走了索引，cost为13.
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
----查询一个范围的执行计划----
SQL> select * from jack where object_id>1000 and object_id<2000;
9880 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 949574992
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9657 | 914K| 1824 (1)| 00:00:22 |
|* 1 | TABLE ACCESS FULL| JACK | 9657 | 914K| 1824 (1)| 00:00:22 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<2000 AND "OBJECT_ID">1000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10993 consistent gets
10340 physical reads
0 redo size
471945 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 ----注意，object_id上是有索引的，但是这里并没有使用索引，而是使用了全表扫描。
SQL> alter system flush buffer_cache;
System altered.
----强制走索引，查看执行计划----
SQL> select /*+ index(jack jack_ind) */ * from jack where object_id>1000 and object_id<2000;
9880 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2860868395