索引压缩（一）

朴灿烈づ我的快乐病毒、 2022-09-30 10:59 101阅读 0赞

**1.创建测试用表t\_compress\_index**

**SQL> create table t\_compress\_index as select \* from all\_objects;  
   
Table created  
 ** ** **

**2.不使用索引压缩技术创建索引**

**SQL> create index idx\_t\_compress\_index on t\_compress\_index(owner,object\_type,object\_name);  
   
Index created**

SQL> analyze index idx\_t\_compress\_index validate structure;  
   
Index analyzed  
   
SQL> select height, lf\_blks, br\_blks, btree\_space, opt\_cmpr\_count, opt\_cmpr\_pctsave from index\_stats;

HEIGHT    LF\_BLKS    BR\_BLKS BTREE\_SPACE OPT\_CMPR\_COUNT OPT\_CMPR\_PCTSAVE  
\---------- ---------- ---------- ----------- -------------- ----------------  
         3        344          3     2774708              2               28

**3.尝试只使用第一列进行压缩**  
**SQL> drop index idx\_t\_compress\_index;**

**   
Index dropped  
 **  
 **   
SQL> create index idx\_t\_compress\_index on t\_compress\_index(owner,object\_type,object\_name) compress 1;  
   
Index created**

**   
SQL> analyze index idx\_t\_compress\_index validate structure;**

**   
Index analyzed**

**   
SQL> select height, lf\_blks, br\_blks, btree\_space, opt\_cmpr\_count, opt\_cmpr\_pctsave from index\_stats;  
   
    HEIGHT    LF\_BLKS    BR\_BLKS BTREE\_SPACE OPT\_CMPR\_COUNT OPT\_CMPR\_PCTSAVE  
\---------- ---------- ---------- ----------- -------------- ----------------  
         3        306          3     2469636              2               19**

**4.尝试使用前两列进行压缩**  
SQL> drop index idx\_t\_compress\_index;

Index dropped  
   
   
SQL> create index idx\_t\_compress\_index on t\_compress\_index(owner,object\_type,object\_name) compress 2;  
   
Index created

SQL> analyze index idx\_t\_compress\_index validate structure;  
   
Index analyzed

SQL> select height, lf\_blks, br\_blks, btree\_space, opt\_cmpr\_count, opt\_cmpr\_pctsave from index\_stats;  
   
    HEIGHT    LF\_BLKS    BR\_BLKS BTREE\_SPACE OPT\_CMPR\_COUNT OPT\_CMPR\_PCTSAVE  
\---------- ---------- ---------- ----------- -------------- ----------------  
         2        246          1     1974060              2                0

**5.尝试使用前三列进行压缩**

SQL> drop index idx\_t\_compress\_index;  
   
Index dropped  
   
SQL> create index idx\_t\_compress\_index on t\_compress\_index(owner,object\_type,object\_name) compress 3;  
   
Index created  
   
SQL> analyze index idx\_t\_compress\_index validate structure;  
   
Index analyzed  
   
SQL> select height, lf\_blks, br\_blks, btree\_space, opt\_cmpr\_count, opt\_cmpr\_pctsave from index\_stats;  
   
    HEIGHT    LF\_BLKS    BR\_BLKS BTREE\_SPACE OPT\_CMPR\_COUNT OPT\_CMPR\_PCTSAVE  
\---------- ---------- ---------- ----------- -------------- ----------------  
         3        385          3     3101004              2               35

**6.索引压缩小结**  
（1）通过上面的这个演示过程，可以得到以下结论：  
1）对前两列进行压缩效果最好  
2）对全部的三列压缩反倒比不使用压缩技术耗用更多的索引空间，这与压缩机制有关  
3）要在实践中反复的测试，得出最佳的压缩系数  
  
（2）索引压缩缺点：  
1.维护索引时，更耗时，因为需要更多的计算  
2.查询时，搜索索引需要较长的时间，因为需要更多的计算  
3.需要更多的CPU处理索引  
4.增加了块竞争  
  
（3）索引压缩好处：  
1.索引占用的磁盘空间少，这是显然的  
2.块缓冲区缓存能存放更多的索引条目  
3.缓存命中率较高  
4.物理I/O较少  
  
任何一种技术都是一种均衡各种资源后的产物，索引压缩技术就充分的体现了这方的特点，需要在disk和CPU之间做到取舍与平衡，需要具体问题具体分析。

**PS：还需要注意一点，就是联合索引中的前向索引重复和唯一的问题，重复数据量的频率也决定了压缩的效率，看看下面的测试结果：**

**7：前缀索引压缩**

SQL> create table t3 as select level L ,'0000'L2 from dual connect by level<=1e6;  
  
表已创建。  
  
已用时间:  00: 00: 04.38  
SQL> create unique index idx3 on t3(l,l2) compress;  
  
索引已创建。  
  
已用时间:  00: 00: 01.27  
SQL> select sum(bytes) from dba\_extents where segment\_name='IDX3';  
  
SUM(BYTES)  
\----------  
   30408704  
  
已用时间:  00: 00: 00.04  
SQL> drop index idx3;  
  
索引已删除。  
  
已用时间:  00: 00: 00.02  
SQL> create unique index idx3 on t3(l,l2);  
  
索引已创建。  
  
已用时间:  00: 00: 01.06  
SQL> select sum(bytes) from dba\_extents where segment\_name='IDX3';  
  
SUM(BYTES)  
\----------  
  24117248  
  
已用时间:  00: 00: 00.03

索引压缩理解：

复合索引前缀列重复度高才能真正实现压缩，在前缀字段的重复项比较多的情况下，压缩索引可以发挥自己的优势。重复项少的情况下，压缩索引可能会占用更多空间。使用压缩索引的前提必须是前缀列的重复项比较多，否则会对性能产生更坏的影响。  
  
把复合索引列顺序反过来，结果看看：  
SQL> create table t2 as select level L ,'0000'L2 from dual connect by level<=1e6;

Table created

SQL> create unique index idx2 on t2(l2,l) compress;  
   
Index created

SQL> select sum(bytes) from dba\_extents where segment\_name='IDX2';  
   
SUM(BYTES)  
\----------  
  17825792

SQL> drop index idx2 ;  
   
Index dropped

SQL> create unique index idx2 on t2(l2,l);  
   
Index created

SQL> select sum(bytes) from dba\_extents where segment\_name='IDX2';  
   
SUM(BYTES)  
\----------  
  24117248