5

In many programming language implementations, the compiler will turn on certain optimizations if it knows that a variable will not change after its initial assignment. Wouldn't this stretch to persistent data too? If the database knows that a certain column will not be updated after a record is created, couldn't it change the way the data is stored, or cached, to improve performance?

  • 1
    It cannot know that you will never update a table - unless you declare it that way. But yes, there might be optimizations based on this expectation (I.e. Lazily allocate transaction locks only if required, etc.) – eckes Sep 22 '17 at 5:07
  • 1
    Databases can have read only tablespaces and filegroups. If the tables are readonly then the indexes are effectively read only as well. Although the database engine may be aware of that i doubt it will affect query performance much. – Sir Swears-a-lot Sep 22 '17 at 6:14
4

Sure, some database platforms do handle read-only or mostly-read data differently.

For example, in Oracle, you can choose to enable results caching, which lets the database engine save query results and reuse them when it sees the same query come in again. That's really useful for data that rarely changes. However, you wouldn't want to bother enabling that in scenarios where you know the data will change.

Another example: in SQL Server, if you set the entire database as read-only, SQL Server handles locking differently. It doesn't need to worry about transactions when you can't modify the data.

I know what you're thinking: "Why can't the database engine figure it out?" Because data access patterns can be complicated. For example, you might have a data warehouse that is loaded from scratch every night, and then is read-only all day long. You wouldn't want the engine to bother even trying to cache ETL query results overnight during the loads, so you'd want to turn that kind of thing off overnight to maximize load speeds.

  • Don't SQL Server's fulltext indexes have a CHANGE_TRACKING = OFF feature that essentially makes them read only? (If I understand that well, have never used it) – ypercubeᵀᴹ Sep 25 '17 at 13:19

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.