【基礎篇索引】索引基礎（四）
再來看下索引的簇因子cluster factor。
通過dba_objects建立4個表，和對應的object_id上的索引，分別為：
t_test_clusterfactor1與dba_objects完全相同
t_test_clusterfactor2偶數的object_id全部減一
t_test_clusterfactor3在t_test_clusterfactor1的基礎上按照object_id排序
t_test_clusterfactor4在t_test_clusterfactor2的基礎上按照object_id排序
具體如下：
SQL> create table t_test_clusterfactor1 as select * from dba_objects;
Table created.
SQL> create table t_test_clusterfactor2 as select * from dba_objects;
Table created.
SQL> update t_test_clusterfactor2 a set a.object_id = object_id - 1 where mod(a.object_id,2) = 0;
27613 rows updated.
SQL> commit;
Commit complete.
SQL> create index ind_test_clusterfactor1 on t_test_clusterfactor1(object_id);
Index created.
SQL> create index ind_test_clusterfactor2 on t_test_clusterfactor2(object_id);
Index created.
SQL>
SQL> create table t_test_clusterfactor3 as select * from dba_objects order by object_id;
Table created.
SQL> create index ind_test_clusterfactor3 on t_test_clusterfactor3(object_id);
Index created.
SQL> create table t_test_clusterfactor4 as select * from t_test_clusterfactor2 order by object_id;
Table created.
SQL> create index ind_test_clusterfactor4 on t_test_clusterfactor4(object_id);
Index created.
分析各個索引的屬性，可以發現索引簇因子與資料的物理分部關係密切，排序後的表的索引簇因子大大降低，只看表2和表4：
SQL> select a.blevel,
2 a.leaf_blocks,
3 a.distinct_keys,
4 a.clustering_factor,
5 a.avg_leaf_blocks_per_key,
6 a.avg_data_blocks_per_key
7 from dba_indexes a
8 where a.index_name = upper('ind_test_clusterfactor2');
BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ------------- ----------------- ----------------------- -----------------------
1 122 28067 1215 1 1
SQL>
SQL> select a.blevel,
2 a.leaf_blocks,
3 a.distinct_keys,
4 a.clustering_factor,
5 a.avg_leaf_blocks_per_key,
6 a.avg_data_blocks_per_key
7 from dba_indexes a
8 where a.index_name = upper('ind_test_clusterfactor4');
BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ----------- ------------- ----------------- ----------------------- -----------------------
1 122 28067 761 1 1
下面具體測試一下，分別使用兩個索引來查詢的具體情況：
1， 查詢某個具體值：
為了避免快取，每次測試之前先清空sharedpool和buffercache
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t_test_clusterfactor2 a where a.object_id = 1;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3383924917
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 |
| |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR2 | 1 | 13 | 1 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
811 recursive calls
0 db block gets
245 consistent gets
470 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t_test_clusterfactor4 a where a.object_id = 1;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1977206600
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 |
| |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR4 | 1 | 13 | 1 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1218 recursive calls
0 db block gets
288 consistent gets
366 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
36 sorts (memory)
0 sorts (disk)
1 rows processed
我們主要關注一下這裡的物理讀和一致性讀，只看一下對比，大致是cf(cluster factor更靠近block數的稍低)。
注意到上述的黑體note，該語句使用的是動態抽樣。事實上這個與優化器模式有關，當前的優化器模式為：
SQL> show parameters optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
SQL>
可以修改一下：
SQL> alter system set optimizer_mode = CHOOSE scope=both;
System altered.
SQL> show parameters optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string CHOOSE
再來看這兩個查詢的話，則情況大不相同：
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t_test_clusterfactor2 a where a.object_id = 1;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3383924917
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR2 |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
640 recursive calls
0 db block gets
107 consistent gets
20 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t_test_clusterfactor4 a where a.object_id = 1;
COUNT(*)
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1977206600
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR4 |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID"=1)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
640 recursive calls
0 db block gets
103 consistent gets
18 physical reads
0 redo size
411 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
這裡有點奇怪，已經設定優化器模式為choose，但是oracle提示卻說基於規則優化（考慮使用cbo），這裡先不細看，只看看cf對物理讀的影響。
2， 再來查詢一段範圍值：
結果顯示與上面大致類似。
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from t_test_clusterfactor2 a where a.object_id >= 1 and a.object_id<= 1000;
COUNT(*)
----------
954
Execution Plan
----------------------------------------------------------
Plan hash value: 3383924917
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 |
| |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR2 | 954 | 12402 | 3 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID">=1 AND "A"."OBJECT_ID"<=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1218 recursive calls
0 db block gets
316 consistent gets
479 physical reads
0 redo size
412 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
36 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> select count(*) from t_test_clusterfactor4 a where a.object_id >= 1 and a.object_id<= 1000;
COUNT(*)
----------
954
Execution Plan
----------------------------------------------------------
Plan hash value: 1977206600
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 1 | 13 | 4 (0
)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 |
| |
|* 2 | INDEX RANGE SCAN| IND_TEST_CLUSTERFACTOR4 | 954 | 12402 | 4 (0
)| 00:00:01 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OBJECT_ID">=1 AND "A"."OBJECT_ID"<=1000)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
811 recursive calls
0 db block gets
221 consistent gets
364 physical reads
0 redo size
412 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-668024/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【基礎篇索引】索引基礎（三）索引
- 【基礎篇索引】索引基礎（二）索引
- 【基礎篇索引】索引基礎（一）索引
- MySQL索引基礎--菜鳥篇MySql索引
- mysql索引基礎MySql索引
- MySQL——索引基礎MySql索引
- mysql基礎_索引MySql索引
- Oracle 基礎--索引Oracle索引
- mysql索引使用基礎MySql索引
- 【基礎知識】索引--點陣圖索引索引
- Mysql基礎 --- 索引+事務MySql索引
- Sql Server系列：索引基礎SQLServer索引
- SQL Server基礎之索引SQLServer索引
- Css基礎學習—索引CSS索引
- 《MySQL 基礎篇》十一：索引的儲存結構MySql索引
- 索引基礎知識總結索引
- oracle spatial之基礎知識之四空間索引Oracle索引
- mysql 索引的基礎操作彙總MySql索引
- Java面試題-基礎篇四Java面試題
- Kafka訊息系統基礎知識索引Kafka索引
- iOS 基礎知識學習目錄索引iOS索引
- 【學習】SQL基礎-018-索引SQL索引
- 索引成本計算的基礎知識索引
- UITableView基礎[ 5 ] 右側索引的實現UIView索引
- Elasticsearch 7.x 之文件、索引和 REST API 【基礎入門篇】Elasticsearch索引RESTAPI
- 《MySQL 基礎篇》四：查詢操作MySql
- 基礎篇
- 【預研】搜尋引擎基礎——inverted index(倒排索引)Index索引
- lucene第一步，lucene基礎，索引建立索引
- 前端基礎入門四（JavaScript基礎）前端JavaScript
- 測試基礎（四）Jmeter基礎使用JMeter
- mysql優化篇（基於索引）MySql優化索引
- 逆向基礎（四）
- Dart基礎（四）Dart
- java基礎（四）Java
- Oracle學習總結--基礎部分（儲存與索引）Oracle索引
- vuex - 基礎篇Vue
- Docker|基礎篇Docker