I have to store a definite set of string values in a column in a large table. You're probably wondering why I don't use another look-up table and set a FK-PK relationship; well imagine there's a good reason for that.
Does Oracle use an compression mechanism for such columns? Or, is there any way to make it use one?
If the answer is negative does Oracle just stores the exact characters for every duplication of values? Can you provide a reference?
As with dates Oracle does not compress data for you.
Setting up a simple environment:
create table test ( str varchar2(100) );
insert all
into test values ('aaa')
into test values ('aba')
into test values ('aab')
into test values ('abb')
into test values ('bbb')
select * from dual;
and using DUMP(), which returns the datatype, the length in bytes and the internal representation of the data, you can see what is stored using this query:
select str, dump(str)
from test
The answer is that in every case 3 bytes are stored.
+-----+-----------------------+
| STR | DUMP(STR) |
+-----+-----------------------+
| aaa | Typ=1 Len=3: 97,97,97 |
| aba | Typ=1 Len=3: 97,98,97 |
| aab | Typ=1 Len=3: 97,97,98 |
| abb | Typ=1 Len=3: 97,98,98 |
| bbb | Typ=1 Len=3: 98,98,98 |
+-----+-----------------------+
As jonearles suggests in the linked answer you can use table compression to reduce the amount of stored bytes, but there are a number of trade offs. Declare your table as follows instead:
create table test ( str varchar2(100) ) compress;
Please note all the warnings in the documentation, and jonearles' answer; there are too many to post here.
It's highly unlikely that you need to save a few bytes in this manner.
