oracle 压缩索引

创建数据：

SQL> create table tb_compress_index  2  (  3      user_year int not null,  4      user_month int not null,  5      user_date int not null,  6      user_name varchar2(20) null  7  );表已创建。SQL> DECLARE  2    V_DATE INT;  3    V_MONTH INT;  4  BEGIN  5    FOR I IN 1..100 LOOP  6      V_DATE := I mod 30;  7      V_DATE := V_DATE + 1;  8      V_MONTH := I mod 12;  9      V_MONTH := V_MONTH + 1; 10      INSERT INTO tb_compress_index VALUES (2010, V_MONTH, V_DATE, 'user_' || I); 11    END LOOP; 12    COMMIT; 13  END; 14  /PL/SQL 该过程已成功完成。

查询：

SQL> WITH compress_index AS (SELECT * FROM tb_compress_index) SELECT * FROM compress_index WHERE ROWNUM < 10; USER_YEAR USER_MONTH  USER_DATE USER_NAME---------- ---------- ---------- --------------------      2010          2          2 user_1      2010          3          3 user_2      2010          4          4 user_3      2010          5          5 user_4      2010          6          6 user_5      2010          7          7 user_6      2010          8          8 user_7      2010          9          9 user_8      2010         10         10 user_9已经选择了9行。SQL>

创建压缩索引，然后查看执行计划：

SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date) compress 3;已经创建了索引。SQL> set autotrace traceonlySQL> set linesize 120SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;已选择8行。执行计划----------------------------------------------------------Plan hash value: 2921924755---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("USER_MONTH"=12)       filter("USER_MONTH"=12)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          4  recursive calls          0  db block gets         11  consistent gets          0  physical reads          0  redo size        613  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          8  rows processed

使用普通索引：

SQL> drop index idx_compress_index;索引已被删除。SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date);已经创建了索引。SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;已选择8行。执行计划----------------------------------------------------------Plan hash value: 2921924755---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("USER_MONTH"=12)       filter("USER_MONTH"=12)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------         66  recursive calls          0  db block gets         29  consistent gets          0  physical reads          0  redo size        613  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          8  rows processedSQL>

多测试几次

使用压缩索引

SQL> drop index idx_compress_index;索引已被删除。SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date) compress 3;已经创建了索引。SQL> alter system flush buffer_cache;系统已更改。SQL> alter system flush shared_pool;系统已更改。SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;已选择8行。执行计划----------------------------------------------------------Plan hash value: 2921924755---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("USER_MONTH"=12)       filter("USER_MONTH"=12)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------       1341  recursive calls          0  db block gets        264  consistent gets         43  physical reads          0  redo size        613  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client         33  sorts (memory)          0  sorts (disk)          8  rows processedSQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;已选择8行。执行计划----------------------------------------------------------Plan hash value: 2921924755---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("USER_MONTH"=12)       filter("USER_MONTH"=12)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          0  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        613  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          8  rows processedSQL>

使用普通索引

SQL> drop index idx_compress_index;索引已被删除。SQL> create index idx_compress_index on tb_compress_index(user_year, user_month, user_date);已经创建了索引。SQL> alter system flush buffer_cache;系统已更改。SQL> alter system flush shared_pool;系统已更改。SQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;已选择8行。执行计划----------------------------------------------------------Plan hash value: 2921924755---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("USER_MONTH"=12)       filter("USER_MONTH"=12)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------       1341  recursive calls          0  db block gets        264  consistent gets         43  physical reads          0  redo size        613  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client         33  sorts (memory)          0  sorts (disk)          8  rows processedSQL> select user_year, user_month, user_date from tb_compress_index where user_month = 12;已选择8行。执行计划----------------------------------------------------------Plan hash value: 2921924755---------------------------------------------------------------------------------------| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT |                    |     8 |   312 |     1   (0)| 00:00:01 ||*  1 |  INDEX FULL SCAN | IDX_COMPRESS_INDEX |     8 |   312 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("USER_MONTH"=12)       filter("USER_MONTH"=12)Note-----   - dynamic sampling used for this statement统计信息----------------------------------------------------------          0  recursive calls          0  db block gets          2  consistent gets          0  physical reads          0  redo size        613  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          8  rows processedSQL>

因此，压缩索引和普通索引在性能上没有区别。

再看存储空间：

SQL> select index_name, compression, leaf_blocks, prefix_length from user_indexes where index_name='IDX_COMPRESS_INDEX';INDEX_NAME                     COMPRESS LEAF_BLOCKS PREFIX_LENGTH------------------------------ -------- ----------- -------------IDX_COMPRESS_INDEX             ENABLED            1             3SQL>SQL> select segment_name, bytes from user_segments where segment_name='IDX_COMPRESS_INDEX';SEGMENT_NAME                                                                           BYTES--------------------------------------------------------------------------------- ----------IDX_COMPRESS_INDEX                                                                     65536SQL> alter index idx_compress_index rebuild nocompress;索引已经改变。SQL> select index_name, compression, leaf_blocks, prefix_length from user_indexes where index_name='IDX_COMPRESS_INDEX';INDEX_NAME                     COMPRESS LEAF_BLOCKS PREFIX_LENGTH------------------------------ -------- ----------- -------------IDX_COMPRESS_INDEX             DISABLED           1SQL> select segment_name, bytes from user_segments where segment_name='IDX_COMPRESS_INDEX';SEGMENT_NAME                                                                           BYTES--------------------------------------------------------------------------------- ----------IDX_COMPRESS_INDEX                                                                     65536SQL>

BYTES在压缩前和压缩后没有改变。

因此，压缩索引不一定能节省空间，就像通常用rar压缩pdf一样，从43MB压缩到42MB，压缩不多。

压缩索引也有几个限制。例如，对于非唯一索引，所有列都可以压缩；对于唯一的索引，除了最后一列。