0

I have been looking at increasing the performance of some tables that have been uploaded to a redshift database. One of the columns in a table has many delimited values which need to be split out and queried at runtime. The table has around 30 million rows and there would be on average 10 delimited values in this particular column.

Coming from an OLTP background my instinct is to normalize these values out to another table. However, I have seen various posts that suggest that this is not the way to go. Looking for some advice

1

In a word, NO!

You should not breach Codd's Rules unless you have no choice but to do so.

I fail to see what performance gains you will obtain by having to parse strings over having an Associative Entity (AKA joining, bridging tables or mapping - amongst others) and using SQL JOINs.

Most importantly from an RDBMS perspective, this allows you to enforce DRI (Declarative Referential Integrity) constraints on your data. DRI is your friend.

From the link:

There are old-wives' tales about FKs being unnecessary overhead. This notion was kept alive by the fact that (for good, but probably now legacy, notably space, reasons) some RDBMSs did not/don't automatically create indexes on Foreign Key fields (this should be done in the majority of cases). This led to the mistaken view that Foreign Keys were overhead without benefit which is not true for the vast bulk of use-cases!

p.s. you mention that I have seen various posts that suggest that this is not the way to go- where are these references? I doubt if they'll stand up to much scrutiny!

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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