Indexes - Expert Oracle Database Architecture - page 440

Index Key Compression
One of the interesting things you can do with a B*Tree index is compress it. This is not compression in the same
manner that ZIP files are compressed; rather, this is compression that removes redundancies from concatenated
(multicolumn) indexes.
We covered compressed key indexes in some detail in the section “Index Organized Tables” in Chapter 10, and
we will take a brief look at them again here. The basic concept behind a compressed key index is that every entry is
broken into two pieces: a prefix and suffix component. The prefix is built on the leading columns of the concatenated
index and will have many repeating values. The suffix is built on the trailing columns in the index key and is the
unique component of the index entry within the prefix.
By way of example, we'll create a table and a concatenated index and measure its space without compression
using ANALYZE INDEX .

■ there is a common misperception that ANALYZE should not be used as a command in Oracle—that the
DBMS_STATS package supersedes it. this is not true. What is true is that ANALYZE should not be used to gather statistics,
but the other capabilities of ANALYZE still apply. the ANALYZE command should be used to perform operations such as
validating the structure of an index (as we will later) or listing chained rows in a table. DBMS_STATS should be used
exclusively to gather statistics on objects.

Note

We'll then re-create the index with index key compression, compressing a different number of key entries, and
see the difference. Let's start with this table and index:

EODA@ORA12CR1> create table t
2 as
3 select * from all_objects
4 where rownum <= 50000;

Table created.
EODA@ORA12CR1> create index t_idx on
2 t(owner,object_type,object_name);
Index created.
EODA@ORA12CR1> analyze index t_idx validate structure;
Index analyzed.

We then create an IDX_STATS table in which to save INDEX_STATS information, and we label the rows in the table
as “noncompressed”:

EODA@ORA12CR1> create table idx_stats
2 as
3 select 'noncompressed' what, a.*
4 from index_stats a;
Table created.

Now, we could realize that the OWNER component is repeated many times, meaning that a single index block in
this index will have dozens of entries, as shown in Figure 11-2 .