Data Compression - Oracle

When you are working with large databases with Oracle Database 10g, there are certain topics with which you need to be familiar. These topics are covered in this article, the first of two parts. It is excerpted from chapter nine of the book Oracle Database 10g A Beginner's Guide, written by Ian Abramson et al (McGraw-Hill/Osborne, 2004; ISBN: 0072230789).

TABLE OF CONTENTS:

By: McGraw-Hill/Osborne

Poor Best

Rating:

/ 26

December 08, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

With data compression, duplicate values in a database block are removed, leaving only a reference to the removed value, which is placed at the beginning of the block. All of the information required to rebuild the data in a block is contained within the block.

By compressing data, physical disk space required is reduced, and disk I/O and memory usage are also reduced, thereby improving performance. However, there are some cases when data compression is not appropriate. The following should be considered when looking at whether or not to compress data:

Does the table exist in an OLTP or data warehousing environment? Data compression is best suited for data that is updated infrequently or, better yet, is read-only. Since most data in a data warehouse is considered read-only, data compression is more compatible with this type of environment.
Does the table have many foreign keys? Foreign keys result in a lot of duplicate values in data. Tables with these structures are ideal candidates for data compression.
How will data be loaded into the table? Even when compression is enabled, data is only compressed during bulk loading (for example, SQL*Loader). If data is loaded using a standard insert into statement, the data will not be compressed.

Compressed Data Objects

Compression can be specified for various data-related objects using the create or alter object commands. Table 9-13 identifies these objects and their first-level parent object, from which default compression properties are inherited if not specified for the base object. For example, if no compression property is specified for a table, it will inherit the property from its tablespace. The same applies to a data partition—if not specified at the partition level, the default property from the table will be used.

Object Type	Compression Property Inheritance Parent
Table	Tablespace
Materialized View	Tablespace
Partition	Table

TABLE 9-13. Compression Property Inheritance

The following listing demonstrates the creation of a table with compression enabled. Line 7 contains the keyword compress to tell Oracle that data compression is to be enabled.

1 create table commission (
2 sales_rep_id     number,
3 prod_id          number,
4 comm_date       date,
5 comm_amt         number(10,2))
6 tablespace comm_ts pctfree 5 initrans 1
maxtrans 255
7 compress;

Because compression can be enabled or disabled at different points in an object’s lifetime (say, by using an alter command), and because the compression action only occurs on new data being loaded, it is possible for an object to contain both compressed and uncompressed data at the same time.

Ask the Expert

Q: Can existing data in a table be compressed and uncompressed?

A: Yes. There are two methods. The first is by using an alter table statement such as

alter table sales
move compress;

The second method is by using the utilities contained in the dbms_redefinition package.

Index Key Compression

Index key compression works in a similar manner to data compression in that duplicated values are removed from the index entries, but is a little more complicated and has more restrictions and considerations than data compression, partly due to the way indexes are structured. Since the details of these structures are beyond the scope of this book, we will focus on the benefits of, and the mechanisms for, defining index compression.

Compressing indexes offer the same benefits as data compression—that is, reduced storage and improved (usually) performance. However, performance may suffer during index scans as the burden on the CPU is increased in order to rebuild the key values. One restriction we should mention is that index compression cannot be used on a unique index that has only one attribute.

Enabling index compression is done using the create index statement. If you need to compress or uncompress an existing index, you must drop the index first and then re-create it with or without the compression option enabled. The following listing illustrates the syntax for creating a compressed index. Table 9-14 provides an explanation of the syntax.

1 create index comm_sr_prod_idx
2 on commission (sales_rep_id, prod_id)
3 compress 1;

Using data and index compression can provide substantial benefits in the areas of storage and performance. In the next section, we will look at how to improve query performance using Oracle Database 10g’s parallel processing options.

Lines	Important Points
1–2	Specify that the index is to be created on columns sales_rep_id and
	prod_id.
3	Specifies that the index is to be compressed, with the number of
	prefixing (leading) columns to compress. In this case, we used a value
	of 1 to indicate that duplicate values of the first column, sales_rep_id,
	are to be removed.

TABLE 9-14. Explanation of Index Compression Syntax

CRITICAL SKILL 9.4

Use Parallel Processing to Improve Performance

Improving performance, and by this we usually mean query performance, is always a hot item with database administrators and users. One of the best and easiest ways to boost performance is to take advantage of the parallel processing option offered by Oracle Database 10g (Enterprise Edition only).

Using normal (that is, serial) processing, the data involved in a single request (for example, user query) is handled by one database process. Using parallel processing, the request is broken down into multiple units to be worked on by multiple database processes. Each process looks at only a portion of the total data for the request. Serial and parallel processing are illustrated in Figures 9-5 and 9-6, respectively.

Parallel processing can help improve performance in situations where large amounts of data need to be examined or processed, such as scanning large tables, joining large tables, creating large indexes and scanning partitioned indexes. In order to realize the benefits of parallel processing, your database environment should not already be running at, or near, capacity. Parallel processing requires more processing, memory, and I/O resources than serial processing. Before implementing parallel processing, you may need to add hardware resources. Let’s forge ahead by looking at the Oracle Database 10g components involved in parallel processing.

Parallel Processing Database Components

Oracle Database 10g’s parallel processing components are the parallel execution coordinator and the parallel execution servers. The parallel execution coordinator is responsible for breaking down the request into as many processes as specified by the request. Each process is passed to a parallel execution server for execution during which only a portion of the total data is worked on. The coordinator then assembles the results from each server and presents the complete results to the requester.