oracle index压缩,Oracle Index Key Compression索引压缩

Index Key

Compression

Oracle Database can use to compress

portions of the primary key column values in a B-tree

index or an index-organized table. Key compression can greatly reduce the space

consumed by the index.

In general, index keys have two pieces, a grouping

piece and a unique piece. Key compression breaks the index key into a prefix entry, which is the grouping piece,

and a suffix entry, which is the unique or nearly unique piece.

The database achieves compression by sharing the prefix entries among the

suffix entries in an index block.

Note:

If a key is not defined to have a

unique piece, then the database provides one by appending a rowid to the

grouping piece.

By default, the prefix of a unique index consists of all

key columns excluding the last one, whereas the prefix of a nonunique index consists

of all key columns. For example, suppose that you create a composite index on

the oe.orders table as follows:

CREATE INDEX orders_mod_stat_ix ON

orders ( order_mode, order_status );

Many repeated values occur in the order_mode and order_status columns. An

index block may have entries as shown in .

Example 3-3

Index Entries in Orders Table

online,0,AAAPvCAAFAAAAFaAAa

online,0,AAAPvCAAFAAAAFaAAg

online,0,AAAPvCAAFAAAAFaAAl

online,2,AAAPvCAAFAAAAFaAAm

online,3,AAAPvCAAFAAAAFaAAq

online,3,AAAPvCAAFAAAAFaAAt

In , the key

prefix would consist of a concatenation of the order_mode and order_status values. If

this index were created with default key compression, then duplicate key

prefixes such as online,0 and online,2 would be

compressed. Conceptually, the database achieves compression as shown in the

following example:

online,0

AAAPvCAAFAAAAFaAAa

AAAPvCAAFAAAAFaAAg

AAAPvCAAFAAAAFaAAl

online,2

AAAPvCAAFAAAAFaAAm

online,3

AAAPvCAAFAAAAFaAAq

AAAPvCAAFAAAAFaAAt

Suffix entries form the compressed version of index rows.

Each suffix entry references a prefix entry, which is stored in the same index

block as the suffix entry.

Alternatively, you could specify a prefix length when

creating a compressed index. For example, if you specified

prefix length 1, then the prefix would be order_mode and the

suffix would be order_status,rowid. For the values in , the index

would factor out duplicate occurrences of online as follows:

online

0,AAAPvCAAFAAAAFaAAa

0,AAAPvCAAFAAAAFaAAg

0,AAAPvCAAFAAAAFaAAl

2,AAAPvCAAFAAAAFaAAm

3,AAAPvCAAFAAAAFaAAq

3,AAAPvCAAFAAAAFaAAt

The index stores a specific prefix once per leaf block at

most. Only keys in the leaf blocks of a B-tree index are compressed. In the

branch blocks the key suffix can be truncated, but the key is not compressed.

See Also:

·     to learn how

to use compressed indexes

·     to learn how to use key compression for partitioned

indexes

·     for

descriptions of the key_compression clause of CREATE INDEX

key_compressionSpecify COMPRESS to enable key

compression, which eliminates repeated occurrence of key column values and may

substantially reduce storage. Use integer to specify the prefix length (number

of prefix columns to compress).

Oracle Database compresses indexes that are nonunique or

unique indexes of at least two columns. If you want to use compression for a

partitioned index, then you must create the index with compression enabled at

the index level. You can subsequently enable and disable the compression

setting for individual partitions of such a partitioned index. You can also

enable and disable compression when rebuilding individual partitions. You can

modify an existing non-partitioned index to enable or disable compression only

when rebuilding the index.

·

For unique indexes, the valid range of prefix length

values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

·

For nonunique indexes, the valid range of prefix length

values is from 1 to the number of key columns.The default prefix length is the number of key columns.

Restriction on Key CompressionYou cannot

specify COMPRESS for a bitmap index.

NOCOMPRESSSpecify NOCOMPRESS to disable

key compression. This is the default.

Compressing an

Index: ExampleTo create the ord_customer_ix_demo index with

the COMPRESS clause, you might issue the following statement:

CREATE INDEX ord_customer_ix_demo

ON orders (customer_id, sales_rep_id)

COMPRESS 1;