I would like to spare some tables in my database.
One table for example has a simple Primary-Key-ID column and a VARCHAR2 column. The VARCHAR2 column has NO duplicate values, yet different unique IDs.
The PK column of this table is just referenced once as a foreign key in another table.
My thoughts are now to insert the values from the VARCHAR2 column into the the table which has held the primary key.
I could now remove the foreign key reference, delete the table and gain a new column with all the (duplicate) VARCHAR2 values. These I would like to compress in a unique/distinct way.
I have heard about index in the Oracle Database to compress column(s) but I am not quite sure which index I need or how to use them...
The underlying feature (and storage savings) should be about as the same as it was with the previous table of unique values and the foreign key reference.
Thank you for your help in advance!
Oracle basic compression allows us to compress tables. It comes with several distinct limitations, not the least of which is that it isn't suitable for OLTP databases. Direct path inserts, updates and deletes don't benefit. So you can't do what you want that way. If your organisation has sprung for the Advanced Compression licence then you have more options, but the compression still works on the table not an individual column.
I think you've confused things with index compression, which does operate on columns, as it allows us to compress the leading column(s) of a compound index. But it's worth applying only when there's a lot of repetition in those columns. If your index has a unique ID for the leading column than compression will actually increase the total amount of space taken. (Just one reason why compound indexes should be built with the least selective column first and the most selective column last.)
Your table is a classic key-value lookup table. So you could consider converting it into an index-organized table. You would save yourself a bit of space by maintaining only a specialized index instead of a table and its primary key index. Find out more