Database Tables - Expert Oracle Database Architecture - page 378

integer that represents the number of columns to compress. This removes repeating values and factors them out at the
block level, so that the values of A and perhaps B that repeat over and over are no longer physically stored. Consider,
for example, a table created like this:

EODA@ORA12CR1> create table iot
2 ( owner, object_type, object_name,
3 primary key(owner,object_type,object_name)
4 )
5 organization index
6 NOCOMPRESS
7 as
8 select distinct owner, object_type, object_name from all_objects
/
Table created.

It you think about it, the value of OWNER is repeated many hundreds of times. Each schema ( OWNER ) tends to own
lots of objects. Even the value pair of OWNER, OBJECT_TYPE repeats many times, so a given schema will have dozens
of tables, dozens of packages, and so on. Only all three columns together do not repeat. We can have Oracle suppress
these repeating values. Instead of having an index block with values shown in Table 10-1 , we could use COMPRESS 2
(factor out the leading two columns) and have a block with the values shown in Table 10-2 .

Table 10-1. Index Leaf Block, NOCOMPRESS

Sys,table,t1 Sys,table,t2 Sys,table,t3 Sys,table,t4

Sys,table,t5 Sys,table,t6 Sys,table,t7 Sys,table,t8

... ... ... ...

Sys,table,t100 Sys,table,t101 Sys,table,t102 Sys,table,t103

Table 10-2. Index Leaf Block, COMPRESS 2

Sys,table t1 t2 t3

t4 t5 ... ...

... t103 t104 ...

t300 t301 t302 t303

That is, the values SYS and TABLE appear once, and then the third column is stored. In this fashion, we can
get many more entries per index block than we could otherwise. This does not decrease concurrency—we are still
operating at the row level in all cases—or functionality at all. It may use slightly more CPU horsepower, as Oracle has
to do more work to put together the keys again. On the other hand, it may significantly reduce I/O and allow more
data to be cached in the buffer cache, since we get more data per block. That is a pretty good tradeoff.
Let's demonstrate the savings by doing a quick test of the preceding CREATE TABLE as SELECT with NOCOMPRESS ,
COMPRESS 1 , and COMPRESS 2 . We'll start by creating our IOT without compression:

EODA@ORA12CR1> create table iot
2 ( owner, object_type, object_name,
3 constraint iot_pk primary key(owner,object_type,object_name)
4 )