1. 创建测试表

-- Create table
drop table alex_t05 purge;
create table alex_t05 (col1 number, name varchar2(100));
drop sequence alex_t05_seq_col1;
create sequence alex_t05_seq_col1
minvalue 1
maxvalue 99999999999999999999
start with 1
increment by 1
cache 200;

-- Create/Recreate primary, unique and foreign key constraints
create index idx_alex_t05_col1 on alex_t05 (col1);

-- Insert table
declare
begin
for i in 1..100000 loop
--insert into alex_t05 values (mod(i,2000), 'alex');
insert into alex_t05 values (alex_t05_seq_col1.nextval, 'alex');
end loop;
commit;
end;
/

--Show table
SQL> desc alex_t05
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 NAME                                               VARCHAR2(100)
SQL> select table_name, index_name, column_name from user_ind_columns where table_name='ALEX_T05';
TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
ALEX_T05             IDX_ALEX_T05_COL1    COL1

2. 分析一下新建索引的索引结构情况。

SQL> analyze index idx_alex_t05_col1 validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'IDX_ALEX_T05_COL1';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                          100

SQL> select segment_name,bytes from user_segments where segment_name='IDX_ALEX_T05_COL1';

SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_ALEX_T05_COL1                 2097152

可以看到索引的空间使用量(PCT_USED)为100%,碎片率为0%,大小为2M。

3. 将索引列数据全部更新一遍,再分析一下索引结构。

SQL> update alex_t05 set col1=col1+3000;

已更新100000行。

SQL> commit;

提交完成。

SQL> analyze index idx_alex_t05_col1 validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'IDX_ALEX_T05_COL1';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 44.84%                       62

SQL> select segment_name,bytes from user_segments where segment_name='IDX_ALEX_T05_COL1';

SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_ALEX_T05_COL1                 5242880

这个时候,发现空间使用量降到62%,碎片率为44.84%,空间变为5M。
此处,说明之前的索引空间存在重用,如果没有重用碎片率应为50%,没有被重用的空间仍占用索引段的空间,成为空洞,所以索引膨胀到5M。

4. 在线重建一下索引。

SQL> alter index idx_alex_t05_col1 rebuild online;

索引已更改。

SQL> analyze index idx_alex_t05_col1 validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'IDX_ALEX_T05_COL1';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                           90

SQL> select segment_name,bytes from user_segments where segment_name='IDX_ALEX_T05_COL1';

SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_ALEX_T05_COL1                 2097152

这个时候,索引段大小和碎片率都回到初始状态了,但是空间使用量为90%,仍有空间浪费,怎么回事呢?
因为该索引非新建的索引,裁剪碎片后,索引块不可能完整充盈。

5. 再试试delete掉全部的记录看看呢。

SQL> delete alex_t05;

已删除100000行。

SQL> commit;

提交完成。

SQL> analyze index idx_alex_t05_col1 validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'IDX_ALEX_T05_COL1';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 100%                         90

SQL> select segment_name,bytes from user_segments where segment_name='IDX_ALEX_T05_COL1';

SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_ALEX_T05_COL1                 2097152

此时,因为没有了数据,所有索引数据都被置为无效,全为碎片,故碎片率为100%。

6. 总结
(1)Insert在索引列上的操作:开辟一个新键值对应节点位置。
(2)Delete在索引列上的操作:索引键值的节点位置标示为删除,空间不回收。
(3)Update在索引列上的操作:Delete + Insert

Trackback

no comment untill now

Add your comment now

切换到手机版