Compression of the data with a view of economy of a place and acceleration of job Oracle

Use compression of the data of the table for economy of a place on a disk and increases of productivity of searches. In the majority of systems of support of decision-making (SPPR) great volumes of the data which are stored{kept} in several very big tables are usually used. At development of similar systems of the requirement to disk space can quickly grow. Now storehouses given in the volume of hundred terabyte meet even more often.

At the decision of problems with the disk space, appeared in oracle 9i release 2 opportunity of compression of the table can reduce essentially volume of the disk space used by tables of a database and, in some cases, to raise{increase} productivity of searches.

In this clause{article} I shall show, as compression of tables works at creation of databases and management of them. I also shall present{introduce} the certain results on productivity, on the basis of results of some tests to help you to understand, what advantages can be received, presumably, at use of compression of tables.

As it is realized

The opportunity of compression of tables in oracle9i release 2 is realized by removal{distance} of duplicated values of the data from tables of base. Compression is carried out at a level of blocks of a database. When the table is determined as compressed, the server reserves a place in each block of a database for storage of one copy of the data meeting in this block in several places. This preserved place name the table of symbols (symbol table). The data marked for compression are stored{kept} only in the table of symbols, instead of in lines of the data. At occurrence in a line of the data marked for compression, in line, instead of the data, the index on the corresponding data in the table of symbols is remembered. The economy of a place is reached{achieved} at the expense of removal{distance} of superfluous copies of values of the data in the table.

Compression of the table or the developer of applications does not influence the user in any way. Developers address to the table equally irrespective of, she is compressed whether or not, therefore sql-searches should not be changed, when you decide to compress the table. Parameters of compression of the table usually are established and change managers or architects of a database, and participation in this process of developers or users minimally.

How to create the compressed table

For creation of the compressed table the keyword compress in the operator create table is used. The keyword compress demands from oracle server, whenever possible, to store{keep} lines of the table in the compressed kind. The example of the operator create table compress is below submitted:

create table sales_history_comp (

part_id varchar2 (50) not null,

store_id varchar2 (50) not null,

sale_date date not null,

quantity number (10,2) not null

)

compress;

It is possible to use also the operator alter table for change of attribute of compression of the existing table, as in the following example:

alter table sales_history_comp compress;

To learn{find out}, whether the keyword compress in definition of the table was used, execute search to performance user_tables the dictionary of the data and check up value of a column compression, as in the following example:

select table_name, compression from user_tables;

table_name compression

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

sales_history disabled

sales_history_comp enabled

The attribute compress also can be set at a level of tabulared space, as at the moment of his{its} creation (with the help of the operator create tablespace), and further (with the help of the operator alter tablespace). The attribute compress is inherited similarly to parameters of storage. At creation of the table in tabulared space the attribute compress this tabulared space is inherited. To define{determine}, whether the attribute compress is set for tabulared space, execute search to performance dba_tablespaces the dictionary of the data and check up value of a column def_tab_compression, as in the following example:

select tablespace_name,

def_tab_compression

from dba_tablespaces;

tablespace_name def_tab_compression

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

data_ts_01 disabled

index_ts_01 disabled

As one would expect, you can compress or not compress the table in tabulared space, irrespective of value compress, the tabulared space set at a level.

Loading of the data in the compressed table

Take into account, that at the instruction{indication} of a keyword compress, as shown in examples is higher, you, actually, do not compress any data. The commands submitted above change only installation in the dictionary of the data. The data really are not compressed, will not be loaded or inserted yet into the table.

Moreover, to guarantee actual compression of the data, it is necessary to use a corresponding method of loading or an insert of the data in the table. Compression of the data occurs only at mass loading or during a mass insert, to the help of one of the following four methods:

Direct loading sql*loader

Consecutive inserts insert with the help append

Parallel insert

create table... as select

Method of direct loading sql*loader - the most convenient way of loading of the data in the table if the data are accessible in a text file. The example is submitted below:

$sqlldr sanjay/sanjay@proddb control=sales_history.ctl direct=true

If the data are accessible in intermediate (staging) to the table, it is possible to use consecutive operators insert with the help append or parallel insert.

As an example we shall consider a case when the entrance data are accessible in not compressed intermediate table sales_history. Using a method of a consecutive insert, it is possible to use the following operator for an insert of the data in the compressed table:

insert / * + append */

into sales_history_comp

select * from sales_history;

For carry of the data from the intermediate table in compressed it is possible to use also parallel insert, is as shown lower:

alter session enable parallel dml;

insert / * + parallel (sales_history_comp, 4) */

into sales_history_comp

select * from sales_history;

Take into account, that at use parallel insert it is necessary to switch on all over again rasparallelivanie operators dml in a session with the help of alter session enable parallel dml command.

If the entrance data are submitted in a usual file, it is possible to address also to it{him} as to the external table, and then to insert the data into the compressed table the same as from the intermediate table. (Discussion of external tables is beyond this clause{article}.)

It is possible to use also the operator create table... as select for creation of the compressed table and an insert in it{her} of the data for one step. An example:

create table sales_history_comp

compress

as select * from sales_history;

If not to use a corresponding method of loading or an insert of the data, the data in the table appear not compressed though for the table and the attribute compress is determined. For example, if to use usual loading (conventional path) with help sql*loader or usual operators insert, data will not be compressed.