【基礎篇索引】索引基礎（二）

接上一篇。繼續索引的基礎原理介紹。

先來新建一個空表，並建立唯一索引，然後在空表中插入測試資料，並注意觀察索引的一些資訊的變化，如下所示：

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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

---

|

--------------------------------------------------------------------------------

---

| 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/，如需轉載，請註明出處，否則將追究法律責任。

【基礎篇索引】索引基礎（二）

相關文章