I come from a PSQL background where materialized views are manually refreshed :)
After skimming Oracle's docs, I'd settle for something as simple as:
CREATE MATERIALIZED VIEW my_mview
REFRESH FAST ON COMMIT
THROTTLE 10s
AS ( ... )
Does Oracle use deltas to refresh these mviews? It'd super cool if it did! For example, if the mview is a sum of a bunch of rows, then it could use the logged changes on those rows and its existing results to make the new mview.
Oracle supported both full refresh and incremental refresh as of at least 10 years ago (maybe longer).
Fast refresh requires creating "MATERIALIZED VIEW LOGS" on the source table(s) and covers most (but not all) aggregations/groupings in the mview SQL. Once it's setup, DML to the source tables gets logged to the mview logs and a "fast" refresh of the mview uses it to incrementally update the mview's data.
> Easy sharding, a la Elasticsearch
Oracle RAC lets you add nodes to the cluster. Helps with CPU - doesn't really help with disk contention
> Fucking SQL. If I want a new feature, then dammit, build on top of SQL the way PostgreSQL has.
There is no good story here for Oracle. Postgres is amazing (and has the potential for more amazing) here. I'm surprised the legions of NoSQL developers aren't contributing to psql.
> For example, I want to make a materialized view that's the result of a query, but that gets updated as new rows get inserted or as the rows it uses gets updated. Let's call it a continuous view or something. Eventual consistency is fine.
This is how (certain) [1] Oracle Materialised Views already work (.. with some effort..) unless I've misunderstood you.
But you have to:
1. Define "logs" on all the included tables (observables)
2. Only define certain types of queries, otherwise they won't dynamically update
3. If not 2, then schedule the updates on a timer/job (eventually consistent)
4. Write your joins with the old syntax (WHERE A.fieldd = (+) B.field)
> I want to be able to choose if a table/db is always in memory or not. I don't care about individual rows - that sounds like someone else's problem
Oracle's In memory feature[0] supposedly offers this though I haven't had a chance to get at it yet.
> While I'm at it, I want a pony, too.
If you've got the money to pay for all the Oracle gear above, then you have more than enough money for a few Ponies ;)
[0] http://www.oracle.com/us/corporate/features/database-in-memo...
[1] https://docs.oracle.com/cd/B28359_01/server.111/b28286/state...