创建数据：
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，压缩不多。
压缩索引也有几个限制。例如，对于非唯一索引，所有列都可以压缩；对于唯一的索引，除了最后一列。
湘公网安备43019002002060