ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 初识表压缩(一)

初识表压缩(一)

原创 Linux操作系统 作者:lsq_008 时间:2008-12-08 15:16:48 0 删除 编辑

在concepts里有如下解释:

Table Compression

Oracle's table compression feature compresses data by eliminating duplicate values in a database block. Compressed data stored in a database block (also known as disk page) is self-contained. That is, all the information needed to re-create the uncompressed data in a block is available within that block. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table.

With the exception of a symbol table at the beginning, compressed database blocks look very much like regular database blocks. All database features and functions that work on regular database blocks also work on compressed database blocks.

Database objects that can be compressed include tables and materialized views. For partitioned tables, you can choose to compress some or all partitions. Compression attributes can be declared for a tablespace, a table, or a partition of a table. If declared at the tablespace level, then all tables created in that tablespace are compressed by default. You can alter the compression attribute for a table (or a partition or tablespace), and the change only applies to new data going into that table. As a result, a single table or partition may contain some compressed blocks and some regular blocks. This guarantees that data size will not increase as a result of compression; in cases where compression could increase the size of a block, it is not applied to that block.

做个测试:

SQL> create table test1(a varchar2(20),b varchar2(20),c varchar2(20))  


Table created.

SQL> insert into test1 select 'AAAAAAAAAAAAAAAAAAAA','BBBBBBBBBBBBBBBBBBBB','CCCCCCCCCCCCCCCCCCCC' from dba_objects where rownum  < 1001;

1000 rows created.

SQL> commit;

Commit complete.

SQL> insert into test1 select * from test1;
 
1000 rows created.
 
SQL> /
 
2000 rows created.
 
SQL> /
 
4000 rows created.
 
SQL> /
 
8000 rows created.
 
SQL> /
 
16000 rows created.
 
SQL> /
 
32000 rows created.
 
SQL> commit;
 
Commit complete.

SQL> create table test_nocompress  tablespace users as select * from test1;
 
Table created.
 
SQL> create table test_compress tablespace users as select * from test1;
 
Table created.
 
SQL> alter table test_compress move tablespace users compress;
 
Table altered.
 
SQL>  select segment_name,segment_type,owner,bytes/1024/1024 from dba_segments where segment_name
  2  in('TEST_NOCOMPRESS','TEST_COMPRESS');

 
SEGMENT_NAME         SEGMENT_TYPE       OWNER                          BYTES/1024/1024
-------------------- ------------------ ------------------------------ ---------------
TEST_NOCOMPRESS      TABLE              SYS                                          5
TEST_COMPRESS        TABLE              SYS                                        .75

可见,在这种极端情况下,压缩表占用的空间比非压缩表小的多。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-506551/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1259073