0345

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

Team Fly

0345