Shulou(Shulou.com)05/31 Report--

This article is about how oracle implements compressed tables. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

How table compression works

In Orcle9i version 2, the table compression feature saves space by removing duplicate data values found in database tables. Compression occurs at the block level of the database. When it is determined that a table is to be compressed, the database reserves space in each database data block to store a single copy of the data that appears in multiple locations in the data block. This reserved space is called a symbol table (symbol table). The data identified for compression is stored only in the symbol table, not in the database row itself. When data identified as being compressed appears in a database row, the row stores a pointer to the related data in the symbol table, rather than the data itself. Space savings are achieved by deleting redundant copies of the data values in the table.

For users or application developers, the effect of table compression is transparent. Whether the table is compressed or not, developers access the table in the same way, so when you decide to compress a table, you don't need to modify the SQL query. Table compression settings are usually configured by database administrators or designers with little developer or user involvement.

1. Table level:

1.1 create a compression table:

The COMPRESS keyword is used when creating the table, and the COMPRESS keyword instructs the Oracle database to store the rows in the table in a compressed format as much as possible.

SQL > create table tmp_test

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

Compress

1.2 modify the existing table to a compressed table:

SQL > alter table tmp_test compress

Uncompress the table:

SQL > alter table tmp_test nocompress

1.3 determine whether the table is compressed:

Determines whether a table uses compression, and the query user_tables,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

two。 Tablespace level:

2.1 create table compressed space:

The COMPRESS attribute can be defined at the tablespace level, either at build time using CREATE TABLESPACE or later using ALTER TABLESPACE.

Like other storage parameters, the COMPRESS property has some inheritance properties. When you create a table in a table space, it inherits the COMPRESS property from that table space.

You can compress or decompress a table directly in a tablespace, regardless of the COMPRESS attribute at the tablespace level.

2.2 convert existing tablespaces to compressed tablespaces SQL > alter tablespace sms default compress

SQL > alter tablespace sms default nocompress

2.3 determine whether a tablespace has been defined using COMPRESS to 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 (tablespaces) is not compressed, it just modifies the settings of the data dictionary; data is compressed only when data is added / inserted into a table.

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

Sql*load of 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 converts 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 extract a compressed table:

SQL > alter table tmp_test move nocompress

5. Compress a materialized view

Use a similar method for compressing tables to compress materialized views.

Materialized views based on joins of multiple tables are usually suitable for compression because they usually have a large number of duplicate 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 properties of a materialized view.

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

SQL > alter materialized view mv_temp_test compress

6. Compress a partitioned table

There are many options when applying 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 this 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 compressed tables

A compressed table can be stored in fewer blocks, saving storage space, and using fewer blocks also means improved performance. Querying a compressed table in a restricted environment is usually faster because they have far fewer database blocks to read.

Use sql*load to load 1 million data:

Table name

Number of rows

Path

Is it compressed?

Time consumed

Test_nocom

1000000

direct

Uncompressed

00:00:21.12

Test_comp

1000000

direct

Compressed

00:00:47.77

It can be seen that adding data to the compressed table takes twice as long as the normal table. The extra time required to load a compressed table comes from the compression operations performed during the data loading process.

It can be concluded that it is possible to use compression techniques on tables that rarely change. When the data in the table changes frequently, try not to use table compression, which affects the insert operation.

Thank you for reading! This is the end of the article on "how to compress tables in oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.