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

超、凢脫俗 2023-01-18 09:08 36阅读 0赞

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;

发表评论取消回复

表情：

评论列表（有 0 条评论，36人围观）

还没有评论，来说两句吧...

相关阅读

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

Index Key Compression Oracle Database can use to compress portions of the primary key

超、凢脫俗/ 2023年01月18日 09:08/ 0 赞/ 37 阅读

相关运维中Oracle 数据压缩(Compression)步骤

本文只是叙述运维中压缩表的步骤，具体原理请自行查阅相关文档，不足之处敬请指正。 --1.查看某个用户下最大的30个表 SELECT \ FROM (SELECT

￡神魔★判官ぃ/ 2023年01月10日 14:48/ 0 赞/ 398 阅读

相关 Oracle Index 索引无效原因

索引无效原因 > 最近遇到一个SQL语句的性能问题，修改功能之前的运行时间平均为0.3s，可是添加新功能后，时间达到了4~5s。虽然几张表的数据量都比较大（都在百万级以上

电玩女神/ 2022年12月27日 09:25/ 0 赞/ 137 阅读

相关 linux compress压缩命令

文章目录 linux compress命令参考 linux compress命令 compress命令使用“Lempress-Ziv”编码压缩数据文

女爷i/ 2022年10月11日 03:59/ 0 赞/ 232 阅读

相关 Oracle 表压缩 - Table Compression

[Oracle的表压缩][Oracle] [Oracle 表压缩(Table Compression)][Oracle _Table Compression]

小鱼儿/ 2022年09月12日 03:55/ 0 赞/ 19 阅读

相关 Oracle INDEX FAST FULL SCAN与 INDEX FULL SCAN

两种常见的索引扫描方式：INDEX FAST FULL SCAN，INDEX FULL SCAN。前者是按照索引的物理结构扫描（就是高水位线以下），后者是按照索引的逻辑结构

淡淡的烟草味﹌/ 2022年08月21日 13:56/ 0 赞/ 221 阅读

相关 Oracle索引(index)的分类、建立与删除

1.索引分类 a) 唯一索引, 作用是数据约束，保证数据唯一，还有就是数据索引，提高查询效率 b)一般索引，只有数据索引的作用，

不念不忘少年蓝@/ 2022年08月13日 16:40/ 0 赞/ 182 阅读

相关 Oracle 10gR2压缩（Compress）技术

Oracle 10gR2压缩（Compress）技术 1、oracle压缩简介... 1 2、oracle压缩优点... 2 3、压缩分类... 2 3.1 表级

以你之姓@/ 2022年05月16日 21:13/ 0 赞/ 178 阅读

相关 Oracle keep index

一个表的主键使用了唯一索引，我直接用 alter table xxx drop constraints pk_xxx; 删除主键，DB扫描时提示说删除约束时必须指定

ゞ浴缸里的玫瑰/ 2022年04月14日 05:28/ 0 赞/ 184 阅读

相关 Oracle_Index

1 Oracle index [Oracle dblink][] [Oracle 数据字典][Oracle] [Oracle 常用系统函数][Oracl

朴灿烈づ我的快乐病毒、/ 2022年02月25日 08:08/ 0 赞/ 216 阅读