Oracle Index Compression Steps

You want to create an index that efficiently handles cases in which many rows have the same values in one or more indexed columns. For example, suppose you have a table defined as follows:

create table cust( cust_id number, last_name varchar2(30), first_name varchar2(30), middle_name varchar2(30));

Furthermore, you inspect the data inserted into the prior table with this query:

SQL> select last_name, first_name, middle_name from cust;

You notice that there is a great deal of duplication in the LAST_NAME and FIRST_NAME columns:

LEE         JOHN     Q

LEE         JOHN     B

LEE         JOHN     A

LEE         JOE     D

SMITH         BOB     A

SMITH         BOB     C

SMITH         BOB     D

SMITH         JOHN     J

SMITH         JOHN     A

SMITH         MIKE     K

SMITH         MIKE     R

SMITH         MIKE     S

You want to create an index that compresses the values so as to compact entries into the blocks. When the index is accessed, the compression will result in fewer block reads and thus improve performance. Specifically you want to create a key-compressed index on the LAST_NAME and FIRST_NAME columns of this table.

Use the COMPRESS N clause to create a compressed index:

SQL> create index cust_cidx1 on cust(last_name, first_name) compress 2;

The prior line of code instructs Oracle to create a compressed index on two columns (LAST_NAME and FIRST_NAME). For this example, if we determined that there was a high degree of duplication only in the first column, we could instruct the COMPRESS N clause to compress only the first column (LAST_NAME) by specifying an integer of 1:

SQL> create index cust_cidx1 on cust(last_name, first_name) compress 1;

Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated. Compressed indexes have the following advantages:

• Reduced storage

• More rows stored in leaf blocks, which can result in less I/O when accessing a compressed index

The degree of compression will vary by the amount of duplication in the index columns specified for compression. You can verify the degree of compression and the number of leaf blocks used by running the following two queries before and after creating an index with compression enabled:

SQL> select sum(bytes) from user_extents where segment_name='&&ind_name';

SQL> select index_name, leaf_blocks from user_indexes where index_name='&&ind_name';

You can verify the index compression is in use and the corresponding prefix length as follows:

select index_name, compression, prefix_length from user_indexes where index_name = 'CUST_CIDX1';

Here’s some sample output indicating that compression is enabled for the index with a prefix length of 2:

INDEX_NAME COMPRESS PREFIX_LENGTH

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

CUST_CIDX1    ENABLED     2

You can modify the prefix length by rebuilding the index. The following code changes the prefix length to 1:

SQL> alter index cust_cidx1 rebuild compress 1;

You can enable or disable compression for an existing index by rebuilding it. This example rebuilds the index with no compression:

SQL> alter index cust_cidx1 rebuild nocompress;

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.