[tlarkworthy]

You have to denormalize in very common cases even for mostly OLTP workloads in order to get sortable data into an index. Consider a cloud storage product with folders connecting with a many to one to documents. The product wants to display the most recently used folders ordered by their inner document modification date.

Because composite indexes commonly can't span tables, you have to push the last modification date up to the folder row in order to get the data in the right place to build the obvious index.

Denormalization is a normal and expected optimization to scale a relational database.

[slavveras]

Couldn't you also use a materialised view here? Or, alternatively, what about an index on the documents table alone? It might depend on whether your db has a flexible enough indexing system to do what you want, but I don't see why the index would have to span tables since it only needs to depend on the folder id and the document modification time

[tlarkworthy]

A materialized view is another approach but thats still essentially denormalization. I prefer using indeces as they are a bit more of a 1st class relational concept in my mind. With a MV you are copying ALL table data, but with an index you can concentrate on just the ordering columns (you can make a few column MV too and then join but you are now reinventing indexes).

You cannot get an index to be used across a join in most relational DB system. But joins pop up everywhere when you normalize.

This query is a fairly distilled example of it https://stackoverflow.com/questions/16402225/index-spanning-...