oracle compression exemplar space

How does it work table compression

In 2nd edition Orcle9i table compression characteristics delete duplicate data values ??found in the database table to save space. Compression level of the database data blocks. When it is determined that a table to be compressed, the database space is reserved in the data block in each database in order to save a single copy of the data in a plurality of positions in the data block. This reserved space is referred to as the symbol table (symbol table). Is identified as to be compressed, the data is only stored in the symbol table, rather than in the database row itself.When is identified as the data to be compressed in a database row, the bank of the symbol table stored in a pointer to a data pointer, rather than the data itself. Save space by removing redundant copies of the data in the table value.

, Table compression effect is transparent for the user or application developers. Regardless of whether the table is compressed developers to access the table in the same way, so when you decide to compress a table, you do not need to modify the SQL query. Table compression settings are typically configured by the database manager or designer, almost no developer or user participation.

1 table level:

1.1 to create a compressed table:

When you create a table, use the COMPRESS keyword the COMPRESS keyword instructs Oracle database as a compressed format to store the rows in the table.

SQL> create table tmp_test

(Id number, phone varchar2 (20), create_time date)


1.2 modify an existing table compression table:

SQL> alter table tmp_test compress;

Cancel table compression:

SQL> alter table tmp_test nocompress;

1.3 determine whether the table is compressed:

Determine a table whether or not to use compression, query user_tables the compression field indicates that the table is compressed.

SQL> select table_name, compression from user_tables where table_name not like ‘BIN%’;







Table space level:

2.1 create a table compression space:

The COMPRESS attribute can be defined in the table space level, either at build time using the CREATE TABLESPACE defined, you can later use ALTER TABLESPACE to define.

COMPRESS attribute also has some inherited characteristics similar to other storage parameters. When you create a table in a table space, it inherited from the table space COMPRESS attribute.

In a table space to compress or decompress a table, without regard to the COMPRESS attribute on the table space level.

2.2 of the existing table space conversion to compressed table space SQL> alter tablespace sms default compress;
SQL> alter tablespace sms default nocompress;

2.3 COMPRESS determine whether the use of a table space definition query USER_TABLESPACES data dictionary view and View DEF_TAB_COMPRESSION column

SQL> select tablespace_name, def_tab_compression from user_tablespaces;

Tablespace DEF_TAB_






Load data to a compressed table

Note: When specified as above, compress the data in the other table (table space) is not compressed, it’s just modify the settings of a data dictionary; compressed data will only plus cut / insert data to a table.

Only use the following four methods, the data in the table will be compressed storage:

Direct path SQL * load
With / * + append * / insert statement
create table .. as select ..
· Parallel insert

Compress a table already exists but not compressed

Use alter table .. move compress to make an existing but not compressed table into a compressed table.

SQL> alter table tmp_test move compress;

Similarly, you can use the alter table .. move nocompress to unpack a compressed table:

SQL> alter table tmp_test move nocompress;

5. Compress a materialized view

Use for compressed table in a similar manner to compress the materialized view.

Materialized view based on multiple tables join generated is usually very suitable for compression, because they usually have a lot of duplicate data entry.

SQL> create materialized view mv_tmp_test



select, b.create_time from tmp_test a, recv_stat b

where =;

You can use the ALTER MATERIALIZED VIEW command to change the attributes of a materialized view compressed.

Please note that when you use this command, usually the next time you refresh the materialized view before the actual compression.

SQL> alter materialized view mv_temp_test compress;

Compression of a partitioned table

Compressed partitioned table applications, can have a variety of choices. You can be applied at the table level compression, compression can also be applied at the partition level.

You can use the ALTER TABLE … MOVE PARTITION command of this district compression

SQL> alter table tmp_test move partition create_200606 compress;

To find out which partitions a table is compressed, you can query the data dictionary view USER_TAB_PARTITIONS


7 compression performance overhead of the table

A compressed table can be stored in fewer data block, thus saving storage space, while using fewer data blocks also means improved performance. In an I / O can usually be done more quickly, because they need to read the database data block is much less subject to certain restrictive environment of a compressed table query.

Use the SQL * load load 1000000 data:

Table name
The number of rows
Whether it is compressed
The time consumed



It can be seen, the data added to the compression table is double the normal table load compressed tables need the extra time from the the compression operation performed in the data loading process.

The conclusion can be drawn: rarely changing table compression technology or the data in the table changes frequently the case, try not to use table compression, it affects the insert operation.