Why isn't there a way to label a column as “keyword”

Question

In a typical star schema, you end up with a bunch of columns that are an ID into some lookup table, where the lookup table is pretty much just this:

create table hostnames ( id primary key, name varchar(200) )

And when you select from your main table, you end up with a bunch of left joins, when you insert, you always have to lookup for the ID first, then insert if it's not there. It's a pain. 50% of my database related code is just for dealing with this.

It would be nice to have a way to indicate to the database that a given column has low number of distinct values, so that it can build its own internal lookup table and save space. The above would become:

create table sometablewithahostname (
    ...
    hostname varchar(200) keyword,
    ...
)

And there is no need to manage a the other tables. Is there a RDBMS system that does that?

Answer 1

If your goal is to de-normalize while mitigating the storage space requirements, Hybrid Column Compression in Oracle 11gR2 can help with this. It is only available on Enterprise Edition with a few specific types of storage.

Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. The database compresses data manipulated by any SQL operation, although compression levels are higher for direct path loads. Database operations work transparently against compressed objects, so no application changes are required.