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:
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.
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 (
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.
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
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.
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.
FIGURE 9-5. Serial processing
FIGURE 9-6. Paralled processing
blog comments powered by Disqus