【基礎篇索引】索引基礎（二）
接上一篇。繼續索引的基礎原理介紹。
先來新建一個空表，並建立唯一索引，然後在空表中插入測試資料，並注意觀察索引的一些資訊的變化，如下所示：
SQL> create table t_test_policy as select * from t_policy where 1=2;
Table created
SQL> create unique index ind_policy_id1 on t_policy(policy_id) tablespace testindex;
SQL> create unique index ind_policy_id1 on t_test_policy(policy_id) tablespace testindex;
Index created
SQL> select a.status from dba_indexes a where a.index_name = upper('ind_policy_id1');
STATUS
--------
VALID
SQL>
SQL> select a.index_name,
2 a.index_type,
3 a.uniqueness,
4 a.blevel,
5 a.leaf_blocks,
6 a.distinct_keys,
7 a.avg_leaf_blocks_per_key,
8 a.avg_data_blocks_per_key,
9 a.buffer_pool
10 from all_indexes a
11 where a.index_name = upper('ind_policy_id1');
INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY BUFFER_POOL
------------------------------ --------------------------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- -----------
IND_POLICY_ID1 NORMAL UNIQUE DEFAULT
SQL> select a.header_file,
2 a.header_block,
3 a.bytes,
4 a.blocks,
5 a.extents,
6 a.segment_name
7 from dba_segments a
8 where a.segment_name = upper('ind_policy_id1');
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS SEGMENT_NAME
----------- ------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
10 12299 65536 8 1 IND_POLICY_ID1
SQL> insert into t_test_policy select * from t_policy where rownum <= 10000;
10000 rows inserted
SQL>
SQL> select a.index_name,
2 a.index_type,
3 a.uniqueness,
4 a.blevel,
5 a.leaf_blocks,
6 a.distinct_keys,
7 a.avg_leaf_blocks_per_key,
8 a.avg_data_blocks_per_key,
9 a.buffer_pool
10 from all_indexes a
11 where a.index_name = upper('ind_policy_id1');
INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY BUFFER_POOL
------------------------------ --------------------------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- -----------
IND_POLICY_ID1 NORMAL UNIQUE DEFAULT
SQL> select a.header_file,
2 a.header_block,
3 a.bytes,
4 a.blocks,
5 a.extents,
6 a.segment_name
7 from dba_segments a
8 where a.segment_name = upper('ind_policy_id1');
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS SEGMENT_NAME
----------- ------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
10 12299 327680 40 5 IND_POLICY_ID1
這裡可以看到擴充套件了4個分別有8個block的extents，
回滾掉：
SQL> rollback;
Rollback complete
再來看看：
SQL>
SQL> select a.index_name,
2 a.index_type,
3 a.uniqueness,
4 a.blevel,
5 a.leaf_blocks,
6 a.distinct_keys,
7 a.avg_leaf_blocks_per_key,
8 a.avg_data_blocks_per_key,
9 a.buffer_pool
10 from all_indexes a
11 where a.index_name = upper('ind_policy_id1');
INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY BUFFER_POOL
------------------------------ --------------------------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- -----------
IND_POLICY_ID1 NORMAL UNIQUE DEFAULT
SQL> select a.header_file,
2 a.header_block,
3 a.bytes,
4 a.blocks,
5 a.extents,
6 a.segment_name
7 from dba_segments a
8 where a.segment_name = upper('ind_policy_id1');
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS SEGMENT_NAME
----------- ------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
10 12299 327680 40 5 IND_POLICY_ID1
SQL>
發現已經分配的extents在rollback時並不回收，再來看看一個查詢及相應的統計資訊：
SQL> select count(*) from t_TEST_policy;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2285505781
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_POLICY_ID1 | 1 | 9 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
98 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
400 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 /*+ full(a)*/ * from t_test_policy a;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 497461044
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 9369 | 194 (1)| 00:00:0
3 |
| 1 | TABLE ACCESS FULL| T_TEST_POLICY | 1 | 9369 | 194 (1)| 00:00:0
3 |
--------------------------------------------------------------------------------
---
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
879 consistent gets
0 physical reads
0 redo size
10879 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
全表掃描一個空表還是有更多的一致性讀，就是高水位的緣故，oracle全表掃描總是讀取到高水位線，儘管沒有commit資料，但是hwm已經拉高了。
平衡樹的幾個特點：
1， 最底層的葉塊(leaf block)儲存了被索引的資料值和對應的 rowid。葉塊之間以雙向連結串列的形式相互連線。位於葉塊之上的索引塊被稱為分支塊(branch block)，分枝塊中包含了指向下層索引塊的指標。
2， 平衡樹(B-tree)內所有葉塊的深度相同，獲取索引內任何位置的資料所需的時間大致相同，時間複雜度為log(n)。
Btree索引的幾個特點：
對於唯一索引，每個索引值對應著唯一的一個 rowid。對於非唯一索引，每個索引值對應著多個已排序的 rowid，故而在非唯一索引中，索引資料是按照索引鍵(index key)及 rowid 共同排序。
鍵值(key value)全部為 NULL 的行不會被索引，只有點陣圖索引(bitmap index)和簇索引(cluster index)例外。在資料表中，如果兩個資料行的全部鍵值都為 NULL，也不會與唯一索引相沖突。
例如：
SQL> create table t_test_uni as select * from dual union all select * from dual;
Table created.
SQL> update t_test_uni set dummy= null;
2 rows updated.
QL> commit
2 ;
Commit complete.
建立唯一索引：
SQL> create unique index ind_t_test_uni on t_test_uni(dummy);
Index created.
看看索引的相關資訊：
SQL> select a.index_name,
2 a.index_type,
3 a.uniqueness,
4 a.blevel,
5 a.leaf_blocks,
6 a.distinct_keys,
7 a.avg_leaf_blocks_per_key,
8 a.avg_data_blocks_per_key,
9 a.buffer_pool
10 from all_indexes a
11 where a.index_name = upper('ind_t_test_uni');
INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY BUFFER_POOL
------------------------------ --------------------------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- -----------
IND_T_TEST_UNI NORMAL UNIQUE 0 0 0 0 0 DEFAULT
SQL> select a.header_file,
2 a.header_block,
3 a.bytes,
4 a.blocks,
5 a.extents,
6 a.segment_name
7 from dba_segments a
8 where a.segment_name = upper('ind_t_test_uni');
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS SEGMENT_NAME
----------- ------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
4 1211 65536 8 1 IND_T_TEST_UNI
SQL>
可以看到葉塊為0，即null值並沒有索引。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-667592/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【基礎篇索引】索引基礎（四）索引
- 【基礎篇索引】索引基礎（三）索引
- 【基礎篇索引】索引基礎（一）索引
- MySQL索引基礎--菜鳥篇MySql索引
- mysql索引基礎MySql索引
- MySQL——索引基礎MySql索引
- mysql基礎_索引MySql索引
- Oracle 基礎--索引Oracle索引
- 【基礎知識】索引--點陣圖索引索引
- Mysql基礎 --- 索引+事務MySql索引
- Sql Server系列：索引基礎SQLServer索引
- SQL Server基礎之索引SQLServer索引
- Css基礎學習—索引CSS索引
- 索引基礎知識總結索引
- mysql 索引的基礎操作彙總MySql索引
- Kafka訊息系統基礎知識索引Kafka索引
- iOS 基礎知識學習目錄索引iOS索引
- 【學習】SQL基礎-018-索引SQL索引
- 索引成本計算的基礎知識索引
- UITableView基礎[ 5 ] 右側索引的實現UIView索引
- Elasticsearch 7.x 之文件、索引和 REST API 【基礎入門篇】Elasticsearch索引RESTAPI
- Java面試題-基礎篇二Java面試題
- Java面試題基礎篇（二）Java面試題
- NIO相關基礎篇二
- 基礎篇
- 【預研】搜尋引擎基礎——inverted index(倒排索引)Index索引
- lucene第一步，lucene基礎，索引建立索引
- 二、Ansible基礎之模組篇
- shell基礎篇(二)-shell變數變數
- mysql優化篇（基於索引）MySql優化索引
- Oracle學習總結--基礎部分（儲存與索引）Oracle索引
- oracle spatial之基礎知識之四空間索引Oracle索引
- vuex - 基礎篇Vue
- Docker|基礎篇Docker
- Maven——基礎篇Maven
- Git——基礎篇Git
- Hbase基礎篇
- Java基礎篇Java