top | item 26219345

(no title)

nchammas | 5 years ago

Of the traditional RDBMSs, I believe Oracle has the most comprehensive support for materialized views, including for incremental refreshes [0].

As early as 2004, developers using Oracle were figuring out how to express complex constraints declaratively (i.e. without using application code or database triggers) by enforcing them on materialized views [1].

It's quite impressive, but this level of sophistication in what materialized views can do and how they are used does not seem to have spread far beyond Oracle.

[0]: https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG...

[1]: https://tonyandrews.blogspot.com/2004/10/enforcing-complex-c...

discuss

kthejoker2|5 years ago

SQL Server has indexed views, which is "real time" refresh since it's a separate clustered index that's written to at the same time as the base tables.

https://www.sqlshack.com/sql-server-indexed-views/

nchammas|5 years ago

Do you know how sophisticated SQL Server is about updating indexed views? How granular are the locks when, for example, an indexed aggregate is updated? That will have a big impact on write performance when there are many concurrent updates.

A long time ago, I tried to use SQL Server indexed views to maintain a bank account balances table based on transaction history [0].

I forget what I ended up doing, but I remember that one of the downsides of using indexed views was that they didn't support any constraints. There are many restrictions on what you can and can't put in a SQL Server indexed view [1].

In this regard, I think Oracle has a more mature materialized view offering, though I personally haven't used Oracle much and don't know how well their materialized views work in practice.

[0]: https://dba.stackexchange.com/q/5608/2660

[1]: https://docs.microsoft.com/en-us/sql/relational-databases/vi...

trollied|5 years ago

As an ex-Oracle DBA, I really do miss native materialized view support in the likes of Postgres. They are /so/ powerful. People love to bash Oracle, but this is a good example of a feature that the open source DBs haven't got close to yet.