[Web Lesson Excerpt]6.2 example demonstrates the effect of data compression

1 Conceptual Understanding

When the system is out of storage space or we want to save storage space, generally think of data compression technology, the use of data compression technology in Oracle system can not only save our storage space, but also reduce our memory footprint, improve our I/O and query speed. Data compression technology is often used in OLAP systems, we know that usually OLAP system General data volume is large, the amount of data loaded at one time, and there is no DML operation, so data compression is more suitable for OLAP systems, but it is important to note that data compression technology will consume more time and information, Especially the cup resource.

The compact table implementation can be implemented from the time the table is created, or an existing table can be converted into a compressed table, which we will demonstrate from both sides.

2 Create a compressed table to verify the compression effect

Create two identical tables, one compressed and one uncompressed by line comparison:

To view table properties:

To view the effect after compression:

From the diagram above, you can see that the compressed table occupies 18 extents, uses 384 data blocks, has 3,145,728 bytes, the table with no compression occupies 24 extents, uses 1152 data blocks, and has 9,437,184 bytes. The comparison found that the compression ratio reached about 30%.

3 compressing a table that already exists

Create a plain data table:

Analyze the test table to count the number of data blocks that are occupied:

Analyze the test table to count the number of data blocks that are occupied:

To decompress a table:

Here we find an interesting phenomenon where we decompress an already compressed table and find that the data block it occupies does not increase.

[Web Lesson Excerpt]6.2 example demonstrates the effect of data compression