在Oracle b-tree索引插入时,最为容易导致热点快的问题,之前也有一些国内大师探索类似问题,因为索引插入会分裂(99-1,5-5),本人觉得比较常见的办法有:

1.减少index block的index entry条目数量(pct free决定),但是会使得索引的存储空间加大,甚至浪费

2.创建Reverse Index,这里探索一下Reverse Index的一些见解。

 

一、相反存储的示例:

—————————————————-
反键存储
DUMP(‘ABC’)
———————-
Typ=96 Len=3: 97,98,99

DUMP(REVERSE(‘ABC’))
———————-
Typ=96 Len=3: 99,98,97

 

 

1.创建基础表

——————————————————————–
表值:ab,bc,cd,de,ef,fg,ge (不能使单个字符,必须为2个字符以上的才能体现Reverse)
2.创建正常的索引,dump

————————
正常索引
row#0[8020] flag: ——, lock: 0, len=12
col 0; len 2; (2):  61 62                                                                                 —key:ab,为ASCII码,附录为转换规则
col 1; len 6; (6):  02 80 02 8a 00 07
row#1[8008] flag: ——, lock: 0, len=12
col 0; len 2; (2):  62 63
col 1; len 6; (6):  02 80 02 8a 00 08
row#2[7996] flag: ——, lock: 0, len=12
col 0; len 2; (2):  63 64
col 1; len 6; (6):  02 80 02 8a 00 09
row#3[7984] flag: ——, lock: 0, len=12
col 0; len 2; (2):  64 65
col 1; len 6; (6):  02 80 02 8a 00 0a
row#4[7972] flag: ——, lock: 0, len=12
col 0; len 2; (2):  65 66
col 1; len 6; (6):  02 80 02 8a 00 0b
row#5[7960] flag: ——, lock: 0, len=12
col 0; len 2; (2):  66 67
col 1; len 6; (6):  02 80 02 8a 00 0c
row#6[7948] flag: ——, lock: 0, len=12
col 0; len 2; (2):  67 65
col 1; len 6; (6):  02 80 02 8a 00 0d
—– end of leaf block dump —–
End dump data blocks tsn: 10 file#: 10 minblk 778 maxblk 778

———————-
Reverse索引
row#0[8020] flag: ——, lock: 2, len=12
col 0; len 2; (2):  62 61                                                                       —反键key:ba
col 1; len 6; (6):  02 80 02 8a 00 07
row#1[8008] flag: ——, lock: 2, len=12
col 0; len 2; (2):  63 62
col 1; len 6; (6):  02 80 02 8a 00 08
row#2[7996] flag: ——, lock: 2, len=12
col 0; len 2; (2):  64 63
col 1; len 6; (6):  02 80 02 8a 00 09
row#3[7984] flag: ——, lock: 2, len=12
col 0; len 2; (2):  65 64
col 1; len 6; (6):  02 80 02 8a 00 0a
row#4[7948] flag: ——, lock: 2, len=12
col 0; len 2; (2):  65 67
col 1; len 6; (6):  02 80 02 8a 00 0d
row#5[7972] flag: ——, lock: 2, len=12
col 0; len 2; (2):  66 65
col 1; len 6; (6):  02 80 02 8a 00 0b
row#6[7960] flag: ——, lock: 2, len=12
col 0; len 2; (2):  67 66
col 1; len 6; (6):  02 80 02 8a 00 0c
—– end of leaf block dump —–
End dump data blocks tsn: 10 file#: 10 minblk 778 maxblk 778

 

结论:

1)Reverse Index只有在多’字符?’key的时候才会表现与Normal Index存储相反,对于单’字符?’在索引中Norma与Reverse存储时一样的。

2)对于ASSM管理方式,Index对应的Key被删除后重新插入,实际的dba位置和以保持一致,及可以立即Reuse删除的空间。

3)对于RAC环境的requence争用,可以采用非sequence的方式,直接在程序集采用程序的唯一值来生成pk,减少争用。

4).减少表争用(多个free lists)

create table t1(id number) storage(freelists 5);
 alter table t1 storage(freelists 5);

5).减少Leaf Block争用 (reverse key)

6).减少插入争用 (freelist group,NO ASSM)
RAC集群中采取多个freelist group可以减少表插入争用
storage(freelist  groups  5)                        

 ==================================

附录一,Index dump内容详解:

Leaf Entries (Unique)–唯一索引
Richard Foote -Index Internals
 
row#0[8025] flag: –D–, lock: 0, len=11, data:(6): 01 40 00 7a 00 2d
col 0; len 2; (2): c1 03

第一行:
Row number (starting at #0) followed by [starting location within block] (行号、Flag标记、锁、索引长度、表行rowid)
Followed by various flags (deletion flag, locking information etc.),这里表面为删除的(D)
Followed by total length of index entry followed by the rowid
第二行:
Index column number (starting at 0) followed by column length followed by column value(索引序列号、表列长度、表Key值)
Repeated for each indexed column
Repeated for each index entryNote: Total overhead is 3 bytes for each leaf index entry (unique index)

 

Leaf Entries (Non-Unique)
Richard Foote -Index Internals
row#0[8019] flag: –D–, lock: 0, len=17
col 0; len 7; (7): 41 43 43 45 53 53 24
col 1; len 6; (6): 01 40 00 0b 00 1d

第一行
Row number (starting at #0) followed by [starting location within block] (行号、Flag标记、锁、索引长度、表行rowid)
Followed by various flags (deletion flag, locking information etc.),这里表面为删除的(D)
Followed by total length of index entry followed by the rowid

第二行
Index column number (starting at 0) followed by column length followed by column value

第三行
Repeated for each indexed column with last column in non-unique index being the rowid of index entry (hence making the index entry effectively unique anyways)[辅助行:保持非唯一所以一直唯一](索引序列号、表列长度、表Key值)
Repeated for each index entry
Note: Total overhead is 4 bytes, 1 more than unique index

 

===================

附录二: