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 (
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 compressionthe 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 (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).
CREATE UNIQUE INDEX test_idx ON test_table(key_a, key_b, key_c)
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) :
whereas a far-from-optimal situation is represented below:
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 (otherwise you'll get one of the errors (ORA-25176 or ORA-14071) explained in the related postings.
primary key (key_a, key_b, key_c)
COMPRESS n -- n must be less or equal than 2 because there are 3 columns in this index
See message translations for ORA-00439 and search additional resources