Oracle compresses table space

How table compression works

In orcle9i 2nd, table compression saves space by deleting duplicate data values found in database tables. Compression is performed at the database block level. When a table is determined to be compressed, the database reserves space in each database data block to store a single copy of data that appears in multiple locations in the data block. The reserved space is called a symbol table ). The data marked as compressed is only stored in the symbol table, rather than in the database row. When data identified as being compressed appears in a database row, the row stores a pointer to the relevant data in the symbol table, rather than the data itself. Space saving is achieved by deleting redundant copies of table data values.

For users or application developers, table compression is transparent. No matter whether the table is compressed or not, developers access the table in the same way. Therefore, when you decide to compress a table, you do not need to modify the SQL query. Table compression is usually configured by database administrators or designers, with almost no involvement of developers or users.

1. Table level:

1.1 create a compressed table:

When creating a table, use the compress keyword. The compress keyword indicates that the Oracle database tries to store rows in the table in a compressed format.

SQL> Create Table tmp_test

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

Compress;

1.2 modify an existing table to a compressed 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 whether a table is compressed. query user_tables. The compression field indicates whether the table is compressed.

SQL> select table_name, compression from user_tables where table_name not like 'bin % ';

Table_name Compress

--------------------------------------

Classes Enabled

Rooms Enabled

Students disabled

Major_stats disabled

 

2. Table space level:

2.1 create a compressed table space:

You can define the compress attribute at the table space level. You can use create tablespace to define the attribute at the table space level, or use alter tablespace to define the attribute at a later time.

Similar to other storage parameters, compress attributes also have some inheritance features. When a table is created in a tablespace, It inherits the compress attribute from the tablespace.

You can directly compress or decompress a table in a tablespace without considering the compress attribute at the tablespace level.

2.2 convert an existing tablespace to a compressed tablespace SQL> alter tablespace SMS default compress;
SQL> alter tablespace SMS default nocompress;

2.3 determine whether a tablespace has been defined using compress. You can query the user_tablespaces data dictionary view and view the def_tab_compression column.

SQL> select tablespace_name, def_tab_compression from user_tablespaces;

TABLESPACE DEF_TAB _

------------------

USERS DISABLED

TEST DISABLED

UNDOTBS01 DISABLED

STATPACK DISABLED

 

 

3. load data into a compressed table

Note: When compress is specified as above, the data in other tables (tablespace) is not compressed. It only modifies the data dictionary settings; data is compressed only when data is added or inserted to a table.

Data in the table is compressed and stored only when the following four methods are used:

SQL * load in the direct path
Insert statement with/* + append */
Create table... as select ..
· Parallel insert

 

 

4. Compress a table that already exists but is not compressed

Use alter table... move compress to convert an existing but uncompressed table to a compressed table.

SQL> alter table tmp_test move compress;

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

SQL> alter table tmp_test move nocompress;

 

 

5. Compress a Materialized View

Compress materialized views in a similar way to compress tables.

Materialized views generated based on the join of multiple tables are usually suitable for compression, because they usually have a large number of repeated data items.

SQL> create materialized view mv_tmp_test

Compress

As

Select a. phone, B. create_time from tmp_test a, recv_stat B

Where a. id = B. id;

You can use the alter materialized view command to change the compression attribute of a materialized view.

When you use this command, note that the actual compression is usually performed the next time you refresh the materialized view.

SQL> alter materialized view mv_temp_test compress;

 

6. Compress a partitioned table

There are many options to apply compression to partitioned tables. You can apply compression at the table level or at the partition level.

You can use the alter table... move partition command to compress the PARTITION.

SQL> alter table tmp_test move partition create_200606 compress;

To find out which partitions in a table are compressed, You can query the data dictionary view USER_TAB_PARTITIONS.

SQL> SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION FROM USER_TAB_PARTITIONS;

 

 

 

7. performance overhead of the compressed table

A compressed table can be stored in fewer data blocks, thus saving storage space. using fewer data blocks also improves performance. Querying a compressed table in an I/O environment with certain restrictions can usually be completed more quickly, because they need to read much less database data blocks.

Use SQL * load to load 1 million of data:

Table Name
Number of rows
Path
Whether it is compressed
Time consumed
 
Test_nocom
1000000
Direct
Non-compressed
00:00:21. 12
 
Test_comp
1000000
Direct
Compressed
00:00:47. 77

It can be seen that the time for adding data to a compressed table is twice that of a normal table. the additional time required for loading a compressed table is the compression operation performed during the data loading process.

It can be concluded that the compression technology is still acceptable for tables with few changes. When the data in the table changes frequently, try not to use table compression, which affects the insert operation.