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))
SQL> insert into test1 select 'AAAAAAAAAAAAAAAAAAAA','BBBBBBBBBBBBBBBBBBBB','CCCCCCCCCCCCCCCCCCCC' from dba_objects where rownum < 1001;
1000 rows created.
SQL> insert into test1 select * from test1;
1000 rows created.
2000 rows created.
4000 rows created.
8000 rows created.
16000 rows created.
32000 rows created.
SQL> create table test_nocompress tablespace users as select * from test1;
SQL> create table test_compress tablespace users as select * from test1;
SQL> alter table test_compress move tablespace users compress;
SQL> select segment_name,segment_type,owner,bytes/1024/1024 from dba_segments where segment_name
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/，如需转载，请注明出处，否则将追究法律责任。