Databases Reference
In-Depth Information
You can disable key compression any time by rebuilding the index with the
nocompress
clause, as
shown here:
SQL> alter index orders_mod_stat_idx rebuild nocompress;
Index altered.
SQL>
You can use key compression to compress one or more partitions of an index, so long as it's a B-tree
index. Here is an example:
SQL> create indexi_cost1 on costs_demop (prod_id) compress local
(partition costsold,partition costs_q1_2003,
partition costs_q2_2003, partition costs_recednt nocompress);
You can specify a number after the
COMPRESS
keyword to tell Oracle how many columns to compress,
as in the following example:
SQL> create index tt2_idx1 on tt2(name,id) compress 2;
Index created.
SQL>
If you don't specify a number after the
COMPRESS
keyword, by default Oracle Database compresses all
columns in a non-unique index. In a unique index, it will compress all columns except the last column.
Key Compression and Storage
Key compression can reduce the number of leaf blocks in an index, as shown in the following example.
First, let's create a regular uncompressed index on two columns in the table objects.
SQL> create index normal_idx on objects(owner,object_name);
Index created.
SQL>
Check the number of leaf blocks in the index with the following command:
SQL> select num_rows,blevel,leaf_blocks from user_indexes
2 where index_name='NORMAL_IDX';
NUM_ROWS BLEVEL LEAF_BLOCKS
---------- ---------- -----------
2555200 2 14589
SQL>
