----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9657 | 914K| 9683 (1)| 00:01:57 |
| 1 | TABLE ACCESS BY INDEX ROWID| JACK | 9657 | 914K| 9683 (1)| 00:01:57 |
|* 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
----------------------------------------------------------
0 recursive calls
0 db block gets
10561 consistent gets
164 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
----强制走索引之后，使用了index range scan,但是cost变成了9683，而全表扫描时是1824.
----还有比较一下两次查询中物理读的情况：全表扫描的物理读明显比索引的要高很多，但是Oracle却没有使用索引。
----因此Oracle认为走索引的Cost比走全表扫描大，而是大N倍，CBO是基于Cost来决定执行计划的。
----由此得出，对于索引的Cost，Oracle是根据clustering factor参数来计算的，而该实验中的clustering factor参数是很高的，数据存储无序。这就造成了Oracle认为走索引的cost比全表扫描的大。
4.2、解决问题：
----通过上面的分析，可以看出，要降低clustering factor才能解决问题，而要解决clustering factor，就需要重新对表的存储位置进行排序。----
----重建jakc表----
SQL> create table echo as select * from jack where 1=0;
Table created.
SQL> insert /*+ append */ into echo select * from jack order by object_id;
725460 rows created.
SQL> commit;
Commit complete.
SQL> truncate table jack;
Table truncated.
SQL> insert /*+ append */ into jack select * from echo;
725460 rows created.
SQL> commit;
Commit complete.
----查看表和索引的信息----
SQL> select segment_name,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK';
SEGMENT_NAME BLOCKS EXTENTS size
------------- ---------- ---------- -----------
JACK 11264 82 88M
SQL> select segment_name,segment_type,blocks,extents,bytes/1024/1024||'M' "size" from user_segments where segment_name='JACK_IND';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS size
------------ ------------------ ---------- ---------- -------------
JACK_IND INDEX 1536 27 12M
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
----对索引进行rebuild----
SQL> alter index jack_ind rebuild;
Index altered.
----查看cluster factor----
SQL> select index_name,clustering_factor,num_rows from user_indexes where index_name='JACK_IND';