ORA-00439: feature not enabled: Table compression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you are getting ORA-00439 for a different feature, you may want to check out other postings concerning this family of error messages.

If you are wondering why you get the error below upon executing a CREATE TABLE like this:

CREATE TABLE test_table (
col_a varchar2(10),
col_b number(5,0)
) COMPRESS
/

ORA-00439: feature not enabled: Table compression

The error can show up with a slightly different text (for version earlier than 10R2 perhaps):

ORA-00439: feature not enabled: Heap segment compression

the reason is very simple: the RDBMS version is not an Enterprise Edition.
In my case it was XE, but the same would happen with a Standard Edition.

But not all is lost: index compression is available across all editions, since version 9i.

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_c varchar2(30)
)
/
CREATE UNIQUE INDEX test_idx ON test_table(key_a, key_b, key_c)
COMPRESS
/

By specifying COMPRESS without a compression prefix, i am actually saying to Oracle to compress the index as much as possible (in this case 2 columns out of 3).
The compression prefix is the number of columns belonging to the index that we want to factor out. This number must be less than the total number of columns that make up the index otherwise you'll get the error ORA-25194, explained in another posting.

If i specify "COMPRESS 1" then Oracle will factor out only the first column of the index. With COMPRESS 2, the first two columns will be packed. This process can be very efficient (from a block usage perspective) when there are many repeating combinations of the columns being compressed, in other words, an ideal situation is when the first two columns tend to be equal across multiple rows (that is the third column is the component varying more frequently) :

key_a	key_b	key_c
AAA	111	1
AAA	111	2
AAA	111	4
AAA	111	100
AAA	112	2
AAA	112	5
AAA	112	7

whereas a far-from-optimal situation is represented below:

key_a	key_b	key_c
AAA	111	1
BBB	121	2
ABC	131	4
CDA	131	100
DDD	142	2
ABD	142	5
CDF	152	7

As you see, in the latter case, values in the first two columns are varying from row to row, thus making the index compression useless if not negative at all.

This is to say that in order to build an efficient database, you must know in advance the data that is going to be stored in the tables. This may seem obvious, but there can be situations where you cannot predict easily how the data will look like, so you must accept (or make you customer accept...) some degree of iterative refinement process if they cannot supply meaningful sample data during the database design phase.

But let's get back to the point: to give an idea using a real case, i had a table with an index made up of 3 columns (with a value distribution similar to the optimal case shown above) and before compressing it, the index was taking up roughly 20Mb (2560 blocks).
With COMPRESS 1, the index size dropped to 14.5 Mb (1792 blocks) and with COMPRESS 2 it shrinked further to 12.2 Mb (1536).
Needless to say, a reduced index size means an increasing probability of having its blocks cached in the buffer, thus improving I/O at the expense of some more CPU time.

Last but not least, COMPRESS can be specified on IOTs as well, just watch out for the correct syntax:

CREATE TABLE test_table (
key_a varchar2(10),
key_b number(5,0),
key_c number(10,0),
col_d varchar2(30),
primary key (key_a, key_b, key_c)
)
ORGANIZATION INDEX
COMPRESS n -- n must be less or equal than 2 because there are 3 columns in this index

otherwise you'll get one of the errors (ORA-25176 or ORA-14071) explained in the related postings.

See message translations for ORA-00439 and search additional resources