全面解析11GR2中的BTree索引（含视频）

zcs0237

列出当前数据库中的键压缩类索引

SYS@zcs11g>  set sqlprompt "_user'@zcs'11g> "

select '--',INDEX_TYPE,index_name from dba_indexes where compression='ENABLED';

--NORMAL                      DR_UNIQ_THS_PHRASE

--IOT - TOP                   MGMT_DB_INIT_PARAMS_ECM_PK

...

90 rows selected.

10.01-索引压缩可以在某种程度上减小索引所占空间、减少叶块数量

索引压缩可以在某种程度上减小索引所占空间，减小扫描索引时候的I/O，提高查询的性能。

一、建表(索引列重复项比较多的时候，可以考虑进行索引压缩）

SYS@ZCS11G>  drop table t3 purge;

create table t3(c1 varchar2(100),c2 varchar2(100),c3 varchar2(100),c4 number);

begin

for i in 1..5000 loop

insert into t3 values(lpad('z',100,'z') ,lpad('c',100,'c'),lpad('s',100,'s'),i);

end loop;

commit;

end;

/

begin

for i in 5001..10000 loop

insert into t3 values(lpad('c',100,'c') ,lpad('s',100,'s'),lpad('z',100,'z'),i);end loop;

commit;

end;

/

begin

for i in 10001..15000 loop

insert into t3 values(lpad('s',100,'s') ,lpad('z',100,'z'),lpad('c',100,'c'),i);

end loop;

commit;

end;

/

set pagesize 0

select '--'||bytes/1024||' KB' from dba_segments where owner='SYS' and segment_name='T3';

--6144 KB

二、无压缩选项时

1、索引占用空间=6144 KB

SYS@ZCS11G>   drop index t3_idx_nozip;

drop index t3_idx_zip;

create index t3_idx_nozip on t3(c1,c2,c3);

select '--'||bytes/1024||' KB' from dba_segments where owner='SYS' and segment_name='T3_IDX_NOZIP';

--6144 KB

2、BLEVEL=3（+叶块=4）

SYS@ZCS11G>   set pagesize 999

select num_rows,blevel,leaf_blocks from user_indexes where index_name='T3_IDX_NOZIP';

--  NUM_ROWS     BLEVEL LEAF_BLOCKS

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

--     15000          3         682

三、压缩前收集统计信息并评估算缩长度

SYS@ZCS11G>   validate index t3_idx_nozip;

select '--',opt_cmpr_count,opt_cmpr_pctsave from index_stats;

--             3               92

--OPT_CMPR_COUNT=3——键最佳压缩长度是3

--OPT_CMPR_PCTSAVE=92——压缩后节省空间的百分比是92%

四、compress——默认认键压缩索引占用空间=256 KB

1、create index … compress

在非唯一索引的情况下，压缩前导列可能是索引中的所有列。

在唯一索引的情况下，压缩前导列可以是索引中除最后一列外的全部列。

SYS@ZCS11G>   drop index t3_idx_nozip;

create index t3_idx_zip on t3(c1,c2,c3) compress;

set pagesize 0;

select '--'||bytes/1024||' KB' from dba_segments where owner='SYS' and segment_name='T3_IDX_ZIP';

--256 KB

2、压缩率确实是跟上面评测的结果很接近

SYS@zcs11g> select '--',ceil((1-256/6144)*100)||'%' from dual;

-- 96%   

3、blevel从3变为1，叶块从92变为25

SYS@ZCS11G>  select num_rows,blevel,leaf_blocks from user_indexes where index_name='T3_IDX_ZIP';

--  NUM_ROWS     BLEVEL LEAF_BLOCKS

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

--     15000          1          25

四、compress  1——非默认键压缩索引,只有一个前导列时占用空间=4096 KB

SYS@ZCS11G>   drop index t3_idx_zip;

create index t3_idx_zip on t3(c1,c2,c3) compress 1;

select '--'||bytes/1024||' KB' from dba_segments where owner='SYS' and segment_name='T3_IDX_ZIP';

--4096 KB

SYS@ZCS11G>   select num_rows,blevel,leaf_blocks from user_indexes where index_name='T3_IDX_ZIP';

--  NUM_ROWS     BLEVEL LEAF_BLOCKS

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

--     15000          2         455

10.02-键压缩原理（暂时没找到带图的oracle压缩算法的文章。。。下面是db2的一篇文章）
摘编：《IBM孔再华的DB2 V9.7 索引压缩原理》

一、未压缩时的索引存储结构

在了解索引压缩的原理之前，我们先了解一下索引是如何存储在数据库中的。

图 1. 原先的索引页

图 1 显示索引页结构。索引的存储和表数据的存储差不多，也是存放在预先定义了大小的数据页中。

页头。每个索引页都是从页头开始，跟着是 SLOT 目录。

SLOT 目录。在 SLOT 目录中，每两个字节对应此索引页中不同的索引键。标识不同索引键在页面中的起始位置。

索引键和记录标识列表。索引在表上定义时，选择创建索引的表列的子集就是索引键。每个索引项还包含相应记录的逻辑指针（RID），也就是记录标识，指到数据表中数据行的位置。

了解了索引的存储结构，我们可以先想象一下，在索引页中有哪些部分是可以被压缩变小的？压缩之后，同样大小的索引页中能否放入更多的数据？在使用索引时，需要什么样的步骤才能达到原先的效果？

二、记录标识列表压缩算法

在图 1 中已经说明了一条索引是由索引键和对应的记录标识列表组成的。记录标识列表是此键值对应的一些 Record ID（RID）。RID 标识记录在表中的位置。一般由 4 到 8 个字节组成。假设在索引页中，某个索引键对应的 RID 列表是‘ 00，00，00，06 ’‘ 00，00，00，07 ’，它标出了对应的两行记录在表中的位置。其实这是两条相邻的记录，第二条记录与第一条记录的增量是 1. 在 DB2 V9.7 中，索引可以存储相邻记录间增量 1 来替换‘ 00，00，00，07 ’这个 RID。转换后存储为‘ 00，00，00，06 ’‘ 1 ’。与原先的标识相比，几乎减少了一半的空间。如果对图 1 中的索引进行 RID list 压缩，就会得图 3 所示结果 .

图 3. RID list 压缩后的索引页

与图 1 相比，记录标识列表中存储的数据发生了很大的变化，除了第一条 RID 是存储的完全记录外，其他的都是存储的记录之间的增量。这个增量可以小到甚至只有 1 字节，与原先需要 4 到 8 个字节的 RID 来标识记录相比，压缩程度是非常可观的。

三、索引键前缀压缩算法

因为索引键是存储在索引中的，对于存储在同一页中的索引键，很有可能相邻的索引键的值差别不大。比如部门编号的前几个数字都是相同的，只有最后两位用了不同的代码区分，比如中国员工的姓名，同姓的人很多，区别是名字不一样。这些信息被存储在表中，并被拿来创建索引。在创建索引或者插入索引时，DB2 会对比相邻的索引键的值，获得其中最长的前缀值。当索引页存满后，这些前缀值又会与相邻的前缀值比较得到优化的前缀值。这些优化的前缀值会被提取出来，存放到 Slot directory 之后。

图 4. 索引键前缀选取


图 5. 索引键前缀压缩

在图中所显示的案例中，DB2 分别挑选了‘ IBM，CN ’和‘ HP，EN，Zha ’作为可压缩的前缀值，存放在 Slot directory 后，然后用‘ 0，2 ’标记提取位置。与行压缩相比，这些前缀的用法与数据字典用法相似，但是前缀只应用于当前索引页中，而数据字典应用于整张表。

zcs0237

11.01-索引的创建语法

一、create ...

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>

      ON <schema>.<table_name>

           (<column_name> | <expression> ASC | DESC,

            <column_name> | <expression> ASC | DESC,...)

     TABLESPACE <tablespace_name>

     STORAGE <storage_settings>

     LOGGING | NOLOGGING

     COMPUTE STATISTICS

     NOCOMPRESS | COMPRESS<nn>

     NOSORT | REVERSE

     PARTITION | GLOBAL PARTITION<partition_setting>

二、相关说明

- UNIQUE | BITMAP：省略为B-Tree索引。

-<column_name> | <expression> ASC | DESC：当为expression时即“基于函数的索引”

-TABLESPACE：为索引指定单独的表空间

-STORAGE：可进一步设置表空间的存储参数

-LOGGING | NOLOGGING：索引用不到REDO LOG

-COMPUTE STATISTICS：创建新索引时收集统计信息

-NOCOMPRESS | COMPRESS<nn>：使用键压缩可以删除一个键列中出现的重复值

-NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值

-PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

11.02-合并与重建索引

一、删除记录行（分析结果视图：index_stats）

SQL>select '--',OBJECT_NAME from user_objects where object_type='INDEX'

--PK_EMP

--PK_DEPT

--EMP_ENAME

--SQL> delete from tab where id>800;

二、分析索引的逻辑坏损（分析结果视图：index_stats）

SQL> analyze index PK_EMP validate structure;

select HEIGHT,BLOCKS,LF_ROWS,DEL_LF_ROWS from index_stats  where NAME= 'PK_EMP';

    树高      索引块数   记录数   删除记录数

    HEIGHT     BLOCKS    LF_ROWS DEL_LF_ROWS

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

         1          8         14           0

三、合并索引——合并同一branch的leaf block（不锁表）以消除碎片但不减树高

SQL> alter  index   索引名  coalesce

过程:  扫描索引的叶了节点-> 相邻的块有碎片进行合并

四、重建索引以消除索引碎片、降低树高

删除记录，对应的索引仅仅是做逻辑删除，只有一个block中的全部索引都被标识为逻辑删除，orcle才会真正的回收block, 这时这个block才能被再次利用。当 DEL_LF_ROWS/ LF_ROWS>15%时应进行 索引重建或索引碎片整理。

SQL> alter index ind_name rebuild [online] tablespace tb1;

11.03-索引的可用性管理

一、unusable索引（不能再被使用也不会随着表的更新而更新）   

SQL> create table t1(id int);

create index t1_idx on t1(id);

select '--'||status from dba_indexes where index_name='T1_IDX';

--VALID

SQL> alter index t1_idx unusable;

select '--'||status from dba_indexes where index_name='T1_IDX';

--UNUSABLE

SQL> alter index t1_idx rebuild;

二、invisible索引（11g，使索引不对CBO可见降低直接删除索引或者禁用索引的风险)

SQL> alter index t_test1_idx invisible;

SQL> alter index t1_idx rebuild;

三、disable仅仅只针对函数索引

SQL> alter t2_idx index_name enable;

SQL> alter t2_idx index_name disable;

zcs0237

--预留位置以便更新、修正

LZ近期文章：

8月13号：表管理知识梳理——普通表的基本优化

http://www.itpub.net/thread-1809208-1-1.html

分析演示各种监听器配置与加固技术（含视频）
http://www.itpub.net/thread-1804092-1-1.html
10gUNDO的10个实验(含视频)
http://www.itpub.net/thread-1793265-1-1.html
DUMP解读LMT的位图块（含视频）
http://www.itpub.net/thread-1799520-1-1.html
表空间日常管理及核心文件搬迁技术（含视频）
http://www.itpub.net/thread-1796584-1-1.html
深入分析并发控制之——事务隔离与锁机制（含视频）
http://www.itpub.net/thread-1795446-1-1.html
实例恢复相关原理精简总结
http://www.itpub.net/thread-1761630-1-1.html

string2020

撸主V5。

zcs0237

string2020 发表于 2013-7-27 22:38
撸主V5。

最先回帖都是高富帅，不用撸

guoyJoe

很详细，很给力，很不错！支持

zcs0237

guoyJoe 发表于 2013-7-27 23:10
很详细，很给力，很不错！支持

花了n多天，改了n多遍才完成
感谢谢郭大师点评。。。

lastwinner

楼主搞培训的？

csz1983

阿范德萨

fengnash

谢谢分享