索引会逐步地膨胀下去,会发肥 肥到超过表大小还不停歇下来的.
索引变肥了 有如下后果
1 占用大量的磁盘空间
2 如果该索引被使用,就要占用大量的BUFFER内存空间
3 对索引范围扫描和快速全索引扫描不利
所以要定期重建索引.
当要怎么样才知道,哪些索引要重建呢?
我有个对比表 比如单列索引的字段大小,和表的平均行大小的比值.
然后索引大小跟表大小的比值. 再拿两个比值对比,看差异是否逐渐扩大.
表:TRADERECORD
索引: IX_TR_DATETIME
索引列大小:11
表平均行大小: 686
列行比率 1.6035 %
索引大小 403 MB
表大小 3932 MB
索引表比率:10.2492 %
表率与字段率 比值 6.391797倍
那么我们该重建此索引. 我们有3个办法来评估新索引的大小.
1 新建一样的表,然后把表的数据插入该新表,在新表上建一样的索引
create table test1 as select * from trade_record;
select * from dba_segments s where s.segment_name='IX_TR_DATETIME';
select * from dba_segments s where s.segment_name='IX_TR_DATETIME2';
旧索引的段信息
SEGMENT_NAME IX_TR_DATETIME
BYTES 212860928
BLOCKS 25984
新索引的段信息
SEGMENT_NAME IX_TR_DATETIME2
BYTES 117440512
BLOCKS 14336
旧的索引大小203MB, 新索引大小112MB
2 使用DBMS_SPACE 包来评估
set serveroutput on;
SQL>
declare
l_index_ddl varchar2(1000);
l_used_bytes number;
l_allocated_bytes number;
begin
dbms_space.create_index_cost
(ddl => 'create index IX_TR_DATETIME3 on ossc.ccps_traderecord(TR_DATETIME) ',
used_bytes => l_used_bytes,
alloc_bytes => l_allocated_bytes);
dbms_output.put_line('used= ' || l_used_bytes || ' bytes' ||
' allocated= ' || l_allocated_bytes || ' bytes');
end;
/
used= 45615955 bytes allocated= 100663296 bytes
PL/SQL procedure successfully completed
allocated= 100663296 bytes =>96MB
3 执行计划
explain plan for create index IX_TR_DATETIME3 on trade_record(TR_DATETIME) ;
SQL> set linesize 200 pagesize 1400;
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1698331371
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 4146K| 43M| 9022 (1)|
| 1 | INDEX BUILD NON UNIQUE| IX_TR_DATETIME3 | | | |
| 2 | SORT CREATE INDEX | | 4146K| 43M| |
| 3 | INDEX FAST FULL SCAN| IX_TR_DATETIME | 4146K| 43M| 6924 (1)|
--------------------------------------------------------------------------------
Note
-----
- estimated index size: 100M bytes
14 rows selected
这里看到评估索引大小为100MB, 使用43MB
索引为什么发福呢?
1 索引分裂
索引是个有序的平衡树,当新来的数据需要插入有序的队伍中
2 索引删除
当更新数据字段的时候,索引先把旧的给打上删除标记,然后在新的地方存新数据
3 并发插入
并发插入的时候 在一定时间内的插入数据 会合并在一定的块里面,然后连接到索引的树上. 而这个块除了PCTFREE规定的空闲空间外,它不会插满的.还有空闲的空间. 这样下去造成大量的索引块都未满.
这就是我这个索引列 是交易时间,少于更新,少于删除,少于分裂,当并发插入还是蛮高的.
那我们看下新旧索引的结构
analyze index IX_TR_DATETIME2 validate structure;
analyze index IX_TR_DATETIME validate structure;
新旧索引差异列
旧 新
LF_BLKS 25039 13315
LF_ROWS_LEN 9554,4075 9,5537,635
LF_BLK_LEN 3988 7966
BR_ROWS 25038 13314
BR_BLKS 59 28
BR_ROWS_LEN 40,4563 21,5709
DEL_LE_ROWS 280 0
DEL_LF_ROWS_LEN 6440 0
BTREE_SPACE 1,0032,9184 1,0669,1524
USED_SPACE 95948638 95753344
PCT_USED 96 90
从对比效果来看得出来,旧的叶块的行长度比新的短很多,那么同样的数据就需要更多的叶块来存,更多的叶块就要更多的分支块
如果本号文对你有意义可以下我打赏，金额多少无所谓！
