Databases Reference
In-Depth Information
The index block shows that the key prefix is a concatenation of the
ORDER_MODE
and
ORDER_STATUS
values. Now, compress the
ORDERS_MOD_STAT_IDX
index using default key compression, as shown here:
SQL> create index orders_mod_stat_idx on orders(order_mode,order_status)
2 compress;
Index created.
SQL>
As a result of the compression, the key prefix, consisting of the concatenation of the
ORDER_MODE
and
ORDER_STATUS
column values, is compressed. All duplicate key prefixes such as online, 0 and online, 2 are
represented by a single, non-repeating value as a result of compression, as shown here:
Online,0
AAAPvCAAFAAAAFaAAa
AAAPvCAAFAAAAFaAAg
AAAPvCAAFAAAAFaAAl
Online,2
AAAPvCAAFAAAAFaAAm
Online,3
AAAPvCAAFAAAAFaAAq
AAAPvCAAFAAAAFaAAt
Compression of the index results in multiple suffix values referencing a single prefix entry. Note that
both the prefix and suffix values are stored in the same index block. This example used default key
compression, which compresses all the columns in the prefix.
Instead, you can specify a prefix length of 1, in which case the prefix would be just the value of the
ORDER_MODE
column. The suffix entry will include both the values of the
ORDER_STATUS
column and the
ROWID
. Here is how to specify a prefix length of 1 when compressing the index key:
SQL> create index orders_mod_stat_idx on orders(order_mode,order_status)
compress 1;
Index created.
SQL>
The number after the
COMPRESS
command (1 in this case) tells Oracle Database how many columns it
should compress. In the case of a non-unique index, the default is all columns. In the case of a unique
index, it is all columns minus one.
The index block will now compress all repeated occurrences of the
ORDER_MODE
column, as shown
here:
0,AAAPvCAAFAAAAFaAAa
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm
3,AAAPvCAAFAAAAFaAAq
3,AAAPvCAAFAAAAFaAAt
In this case, at most, the index will store a prefix once per leaf block.
