使用compress压缩表

hxl

-- 创建表

1. create table TB_CP_TEST
   
2. (
   
3.   ID NUMBER not null,
   
4.   NAME1 CHAR(2000) default 'A',
   
5.   NAME2 CHAR(2000) default 'A',
   
6.   NAME3 CHAR(2000) default 'A',
   
7.   NAME4 CHAR(2000) default 'A',
   
8.   NAME5 CHAR(2000) default 'A',
   
9.   NAME6 CHAR(2000) default 'A',
   
10.   NAME7 CHAR(2000) default 'A'
    
11. )
    
12. ;

-- 写入数据

1. Declare
   
2. Begin
   
3.   For i In 1 .. 10000 Loop
   
4.     Insert Into Tb_Cp_Test (Id) Values (i);
   
5.     Commit;
   
6.   End Loop;
   
7. End;
   
8. /

-- 查看表占用空间大小

1. Select a.BYTES
   
2. From dba_segments a
   
3. Where a.segment_name = 'TB_CP_TEST';
   
4. 
   
5. BYTES
   
6. ----------
   
7. 
   
8.  251658240

-- 将表修改为可以压缩

1. Alter Table TB_CP_TEST compress;

-- 压缩表

1. alter table TB_CP_TEST move compress;

-- 查看表压缩后占用空间大小

压缩前后占用空间大小分别是251,658,240 150,994,944 可见压缩能节省不少空间,但表压缩后对表进行DML语句会消耗更多的io和cpu,同时表压缩后,索引会失效。

1. Select a.BYTES
   
2. From dba_segments a
   
3. Where a.segment_name = 'TB_CP_TEST';
   
4. 
   
5. BYTES
   
6. ----------
   
7. 
   
8. 150994944

11gR2以后的版本可以通过dbms_compression包来查看压缩后的比例.

1. Create Table tb_compress_test
   
2. As
   
3. Select * From dba_objects;
   
4. 
   
5. Alter Table tb_compress_test Move Compress;
   
6. 
   
7. DECLARE
   
8.     blkcnt_comp PLS_INTEGER;
   
9.     blkcnt_uncm PLS_INTEGER;
   
10.     row_comp PLS_INTEGER;
    
11.     row_uncm PLS_INTEGER;
    
12.     comp_ratio number;
    
13.     comp_type VARCHAR2(30);
    
14.   BEGIN
    
15.      dbms_compression.get_compression_ratio(
    
16.                                            scratchtbsname => 'TEST',
    
17.                                            ownname => 'HXL',
    
18.                                            tabname => 'TB_COMPRESS_TEST',
    
19.                                            partname => NULL,
    
20.                                            comptype => dbms_compression.comp_for_oltp,
    
21.                                            blkcnt_cmp => blkcnt_comp,
    
22.                                            blkcnt_uncmp => blkcnt_uncm,
    
23.                                            row_cmp => row_comp,
    
24.                                            row_uncmp => row_uncm,
    
25.                                            cmp_ratio => comp_ratio,
    
26.                                            comptype_str => comp_type);
    
27.     dbms_output.put_line('Block Count Compressed: ' || TO_CHAR(blkcnt_comp));
    
28.     dbms_output.put_line('Block Count UnCompressed: ' || TO_CHAR(blkcnt_uncm));
    
29.     dbms_output.put_line('Row Count Compressed: ' || TO_CHAR(row_comp));
    
30.     dbms_output.put_line('Row Count UnCompressed: ' || TO_CHAR(row_uncm));
    
31.     dbms_output.put_line('Block Count Compressed: ' || TO_CHAR(comp_ratio));
    
32.     dbms_output.put_line('Compression Type: ' || comp_type);
    
33.  END;
    
34. 
    
35. Block Count Compressed: 353
    
36. Block Count UnCompressed: 1091
    
37. Row Count Compressed: 216
    
38. Row Count UnCompressed: 69
    
39. Block Count Compressed: 3
    
40. Compression Type: "Compress For OLTP"