When to use compression of tables

Algorithm of decision-making used by oracle server about that to compress the given tables or to not compress, results in the certain conclusions about features of the applications most of all suitable to compression of tables. As it has been described above, the data in the table with attribute compress are compressed only at direct loading or at an insert with use of the help append and rasparallelivaniem. The data inserted by usual operators insert, will stay not compressed.

In systems of operative processing transactions (online transaction processing - oltp) the data are usually inserted by usual operators insert. In result, from use of compression for corresponding tables of the big advantage will not be. Compression of tables most of all approaches for tables only for the reading, given in which are loaded once, and read - repeatedly. The tables used at the organization of storehouses of the data, for example, fine approach for compression.

Moreover, change of the data in the compressed table can demand unpacking of lines that brings to nothing all advantages of compression. In the result, often changeable tables badly approach for compression.

At last, it is necessary to take into account consequences of removal{distance} of a line at use of compression of the table. At removal{distance} of a line in the compressed table the server releases{exempts} a place borrowed{occupied} in the line in the block. This empty seat can be repeatedly used at any subsequent insert. However as the line inserted in a usual mode, is not compressed, it is improbable, that she will be placed in a place released{exempted} from a compressed line. The significant amount of consistently carried out operators delete and insert can call a fragmentation and disk space thus it will be vain to be used more, than it was possible to save at the expense of compression.

Compression of the existing not compressed table

Already existing not compressed table can be compressed with the help of the operator alter table... move. For example, not compressed table sales_history_temp can be compressed with the help of the following operator:

alter table sales_history_temp

move compress;

The operator alter table... move it is possible to use and for a cancellation of compression of the table, as in the following example:

alter table sales_history_temp

move nocompress;

Take into account, that the operator alter table... move establishes EXCLUSIVE blocking the table that prevents performance of any operators dml with the table for the period of performance of this operator. This potential problem can be avoided at the expense of use of operative redefinition of the table (online table redefinition), appeared in oracle9i.

Compression of materialized performance

It is possible to compress materialized performances the same way, as well as tables. The following operator creates the compressed materialized performance:

create materialized view mv_sales_comp compress as select p.part_name, h.store_id, h.sale_date, h.quantity from sales_history h, parts p where p.part_id = h.part_id;

Materialized performances on the basis of connections of several tables usually well give in to compression as in them often there are repeating components of the data. The attribute of compression for materialized performance can be changed with the help of the operator alter materialized view. The following operator shows how to compress the existing not compressed materialized performance:

alter materialized view mv_sales compress;

At use of this operator take into account, that compression actually will take place at the following updating materialized performance.

Compression partitioned tablicy1

Use of compression for the partitioned tables it is a lot of variants. Compression can be applied or at a level of the table, or at a level of section. For example, the operator create table in Listing 1 creates the table of four sekcij. As compress it is set at a level of the table, all four sections will be compressed.

As compression can be set at a level of section, it is possible for section to compress the some people, and others to leave not compressed. The example in Listing 2 shows how to set compression at a level of section.

In Listing 2 two sections of the table (sales_q1_03 and sales_q2_03) are compressed, and other two remain not compressed. Take into account, that the attributes of compression set at a level of section, redefine the attributes set for same section at a level of the table. If the attribute of compression for section is not set, this section inherits value from definition at a level of the table. In Listing 2 as attributes of compression for sekcij sales_q3_03 and sales_q4_03 are not specified, these two sections inherit value from definition of the table (which, in this case, standardly - nocompress).

The partitioned tables provide one unique advantage together with compression. One of useful ways to partition tables - to place subject to change (to an insert, updating and removal{distance}) the data in separate section, and the data only for reading to bear{take out} in others. For example, in definition of the table in Listing 2 data on sales are partitioned on value of a column sale_date so the chronological information on sales in each quarter is stored{kept} in separate section. In this example the data on sales for the first (q1) and (q2) quarters 2003 cannot be changed by the second, therefore they are placed in compressed section sales_q1_03 and sales_q2_03. The data on sales for the third (q3) and the fourth (q4) quarters can still vary, therefore corresponding to section, sales_q3_03 and sales_q4_03, are left not compressed.

If at the end of the third quarter 2003 the data in section sales_q3_03 become accessible only for reading, it is possible to compress this section with the help of the operator alter table... move partition, is as shown lower:

alter table sales_part_comp

move partition sales_q3_03 compress;

To learn{find out}, what section of the table are compressed, it is possible to execute search to performance user_tab_partitions the dictionary of the data, as in the following example:

select table_name, partition_name,

compression

from user_tab_partitions;

table_name partition_name compression

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

sales_part_comp sales_q4_03 disabled

sales_part_comp sales_q1_03 enabled

sales_part_comp sales_q2_03 enabled

sales_part_comp sales_q3_03 enabled

Rating of advantages

Principal cause of use of compression of the table is the economy of disk space. The table in the compressed kind usually borrows{occupies} less places. To illustrate this statement, we shall consider the following with two tables: one not compressed (sales_history), and another - compressed (sales_history_comp). The data have been loaded in both these tables with the help of direct loading by the utility sql*loader from the text file, containing two million lines. After performance of both loadings appeared, that the compressed table borrows{occupies} on a disk almost twice less places, than not compressed. The analysis is submitted in Listing 3.

That fact, that for storage of the compressed table is necessary less blocks, results in economy of disk space, but reduction of quantity{amount} of blocks can result and in increase of productivity. Searches to the compressed table in the environment with the limited productivity of input-output often will be carried out faster as perusal of smaller quantity{amount} of blocks demand. To illustrate this statement, I have executed search to the compressed and not compressed table and have analysed results sqltrace/tkprof. These results are submitted in Listing 4.

The report sqltrace/tkprof shows, that my search to the compressed table has demanded less operations of physical and logic input-output, than similar search to not compressed table, and as consequence{investigation}, is carried out faster.

Decline of productivity at loading

As compression of the table is carried out at mass loading, operations of loading demand additional processing - it is necessary to carry out additional actions. To measure influence of compression on productivity, I have executed the test, in which loaded one million lines (with the help of direct loading by the utility sql*loader) into two identical tables: with compression and without compression. In Table 1 the results taken from journal files sql*loader and showing are submitted, what is the time it was required for loading the data in each of tables.

Name of table Kolichest-in lines the Way of loading Compressed? Time of loading

sales_history 1000000 Neposred-stvennaja not compressed 00:00:21.12

sales_history_comp 1000000 Neposred-stvennaja Compressed 00:00:47.77

Table 1: Comparison of time of loading of the data for the compressed and not compressed tables

Additional time at loading in the compressed table is required for performance of actions on compression of the loaded data. In a real situation distinction in time of loading will depend on features of the table and the loaded data.

The conclusion

Compression of the table in oracle9i release 2 allows to save essentially disk space, is especial in the databases containing the big tables only for reading. If to take into account additional requirements to loading and an insert of the data, and also correctly to choose tables - candidates for compression, compression of tables can appear tremendous way of economy of disk space and, in some cases, increases of productivity of searches.