Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Column-oriented systems do that. Example: Infobright –  ypercube May 8 '12 at 17:25

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.