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

最新推荐文章于 2024-07-30 13:42:46 发布

cixlv48826

最新推荐文章于 2024-07-30 13:42:46 发布

阅读量79

点赞数

文章标签：数据结构与算法数据库

接上一篇。继续索引的基础原理介绍。

先来新建一个空表，并建立唯一索引，然后在空表中插入测试数据，并注意观察索引的一些信息的变化，如下所示：

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(*)

----------

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-667592/

cixlv48826

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【基础篇索引】索引基础（二）

接上一篇。继续索引的基础原理介绍。先来新建一个空表，并建立唯一索引，然后在空表中插入测试数据，并注意观察索引的一些信息的变化，如下所示：SQL> create table t_test_policy as sele...
复制链接

扫一扫