Databases Reference
In-Depth Information
The database also has to deal with the additional burden of maintaining the large prefix table. You can
potentially end up in a situation where the compressed index is larger than the uncompressed index!
An extreme example of a very selective index is a single column unique index where by definition
there can't be duplicate column values. Each index row entry will have a separate prefix index entry, and
thus the compressed index will be larger than the uncompressed version. Actually, Oracle doesn't even
allow you to do this, because it issues an error if you try to use the
COMPRESS
option for a single column
unique index, as the following two examples demonstrate. In the first example, you can compress an
unique index on two columns (
name,id
) without a problem, but the database won't allow you to
compress a single column (column
name
in this example) unique index.
SQL> create unique index tt2_idx1 on tt2(name,id) compress
SQL> /
Index created.
SQL> create unique index tt2_idx3 on tt2(name) compress;
create unique index tt2_idx3 on tt2(name) compress
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key
SQL>
In general, remember that the more heavily repeated a column value, the higher the storage savings
when you compress the index. In a composite index, ensure that the low cardinality columns are the
leading columns for compression to show good results.
Creating a Compressed Index
Let's make use of the following example to understand how key compression helps reduce storage in an
index. Let's create a composite index on the
ORDERS
table (OE schema) on the columns
ORDER_MODE
and
ORDER_STATUS
.
SQL> create index orders_mod_stat_idx on orders(order_mode,order_status);
Index created.
SQL>
Once you create this composite index, an index block will have the following entries:
Online,0,AAAPvCAAFAAAAFaAAa
Online,0,AAAPvCAAFAAAAFaAAg
Online,0,AAAPvCAAFAAAAFaAAl
Online,2,AAAPvCAAFAAAAFaAAm
Online,3,AAAPvCAAFAAAAFaAAq
Online,3,AAAPvCAAFAAAAFaAAt
