IOTs on Oracle Database 23c: What’s old is new again? Part 2

In part 1 of the mini-series on Oracle Database Index-Organized tables (IOTs), I reviewed some of the pitfalls of using IOTs, especially on an Exadata platform. On Oracle database 19c and earlier, one of the biggest problems with IOTs is that IOTs do not support advanced index compression (COMPRESS ADVANCED LOW or COMPRESS ADVANCED HIGH), though you can use prefix compression with IOTs.

New Options in 23c

In Oracle Database 23c (you can install the free developer edition right now), IOTs can be compressed much like an index on a heap table. My first attempt to create a compressed IOT failed with the assumption that COMPRESS ADVANCED HIGH was also available:

	"LO_SHIPMODE" CHAR(10 BYTE),
    constraint pk_iot_pref_lineorder
       primary key (lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate)
   ) 
   organization index
   compress advanced high
Error report -
ORA-25195: invalid option for index on an index-organized table
25195. 00000 -  "invalid option for index on an index-organized table"
*Cause:    An attempt to specify one or more of the following options for
           index on an IOT:
           BITMAP, REVERSE, PCTUSED

For now, only COMPRESS ADVANCED LOW is available, and I created the IOT successfully:

CREATE TABLE "IOT_LINEORDER" 
   ("LO_ORDERKEY" NUMBER NOT NULL ENABLE, 
. . .
	"LO_SHIPMODE" CHAR(10 BYTE),
    constraint pk_iot_pref_lineorder
       primary key (lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate)
   ) 
   organization index
   compress advanced low
;

Table "IOT_LINEORDER" created.

Segment Size Comparison

The question is, when can an IOT with COMPRESS ADVANCED LOW match or exceed the space reduction of a heap-based table with an index compressed using the same compression method?

I ran compression tests using two different dimensions: two different orders for the primary key:

(lo_orderkey,lo_linenumber,lo_custkey,lo_partkey,lo_suppkey,lo_orderdate)
(lo_linenumber,lo_orderdate,lo_custkey,lo_partkey,lo_suppkey,lo_orderkey)

And three different OVERFLOW segment options:

• No overflow segment: all columns reside in the same segment as the primary key
• Two columns reside in the index segment, the rest in the overflow segment
• All non-key columns reside in the overflow segment

Compression Comparison

The results of the various IOT configurations and compression options were not too surprising.

The uncompressed versions of the heap table and IOT show the advantage of putting both the table and the index in the same segment:

However, when I compressed the heap table with Advanced Compression, its index with Compress Advanced Low, and the IOT with Compress Advanced Low, I saw a significant reduction in the heap table’s size but I didn’t see any reduction in the IOT’s size:

It wasn’t until I changed the order of the columns in the index that the IOT compression was working (which also reduced the size of the heap table and index):

Finally, when using overflow segments, the IOT compression still happens but the total size is larger since the index segment must store physical ROWIDs that reference the corresponding rows in the overflow segment. Again, the key column order makes a difference as well.

Summary and Next Steps

Having the COMPRESS ADVANCED LOW option for IOTs is useful for leveraging the same compression capabilities available to standard indexes. However, this capability is somewhat limited in two ways — first, since an IOT’s primary key index must be unique by definition, in many cases the columns that comprise the index may have more distinct values, limiting the compression ratio. Second, since an IOT likely has some or all columns stored in the index itself, there will also be fewer opportunities in each block for duplicate values.

Stay tuned for part 3 where I investigate how compressed IOTs may or may not perform well in an Exadata environment!