You can't. The feature is disabled in 2017 RTM.
That said, you can...
CREATE VIEW dbo.TH
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
ON TH.ProductID = P.ProductID
GROUP BY P.ProductID;
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID)
Looks like 9.3 and up you can do:
select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';
More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view
As you've noted, the view itself only materializes a small number of rows - so even if you update the whole table, the additional I/O involved with updating the view is negligible. You probably already felt the biggest pain you're going to feel when you created the view. The next closest will be if you add a gazillion rows to the base table with a bunch of ...
CAST is not deterministic because date format may change based on the server settings (i.e. under compatibility before 110 default date format is 0 => "Dec 12 2019 2:11PM" and output dependeds on language).
To make it deterministic use CONVERT and
the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic,...
You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3.
You can create a plain VIEW:
CREATE VIEW graph_avg_view AS
SELECT xaxis, AVG(value) AS avg_val
GROUP BY xaxis;
And materialize the result once or whenever you need to start over:
This is assuming that materialized views have relpages >= 8 in pg_class, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE. The corresponding disk file has a zero size in this case.
SELECT relname AS objectname
, relkind AS objecttype
, reltuples AS ...
This happens often if the optimiser thinks it can do better with the base tables.
If I create an indexed view, I tend to always use NOEXPAND to make it use it. I also compare the query statistics to ensure that it adds some benefit. That is, if I identify a useful indexed view I want the optimiser to always use it.
SET STATISTICS IO ON;
I build indexed views in SQL Server all the time to tune existing products. The optimizer is smart enough to use the index if you are utilizing the appropriate columns.
Using your example, it looks like you created the view but did not actually create an index upon it.
if object_id(N'mytable1') is not null
drop table mytable1
if object_id(N'mytable2') ...
The following explanation is given in this Microsoft Technical Article:
Why does the first index on a view have to be CLUSTERED and UNIQUE?
It must be UNIQUE to allow easy lookup of records in the view by key value during indexed view maintenance, and to prevent creation of views with duplicates, which would require special logic to maintain. It must be ...
You can do this with a function that runs in the security context of its owner.
Function that refreshes the view (create it with the user that owns the MV/table):
CREATE OR REPLACE FUNCTION refresh_mvw1()
REFRESH MATERIALIZED VIEW mvw1 with data;
$$ LANGUAGE plpgsql;
Grant execute on the function to ...
As of PostgreSQL 9.4: Different to the documentation of CREATE VIEW, the documentation of CREATE MATERIALIZED VIEW does NOT mention the REPLACE keyword. There seems to be no shortcut aside from dropping all dependent objects and rebuilding each one.
When you do so, I can only recommend two small things:
Use DROP MATERIALIZED VIEW blabla CASCADE to get a ...
Aarons answers covered this question well. Two things to add:
Aggregation indexed views can lead to cross-row contention and deadlocks. Normally, two inserts do not deadlock (except for rather rare conditions such as lock escalation or lock hash collisions). But if both inserts address the same group in the view they will contend. The same point stands for ...
There is no setting to change this behavior nor has it changed in newer SQL Server versions. An excerpt from the current documentation remarks section for SQL Server 2019 and Azure SQL Database:
ALTER VIEW can be applied to indexed views; however, ALTER VIEW
unconditionally drops all indexes on the view
Concurrent Update (Postgres 9.4)
While not an incremental update as you asked for, Postgres 9.4 does provide a new concurrent update feature.
To quote the doc…
Prior to PostgreSQL 9.4, refreshing a materialized view meant locking the entire table, and therefore preventing anything querying it, and if a refresh took a long time to acquire the exclusive ...
The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.
The plan produced by the query processor incorrectly handles NULLs for the ImageObjectID column. It incorrectly reasons that the view query rejects NULLs for this column, when ...
A view is just a "saved query". The indexes on the base table are still used whenever you access the view.
You don't need to use an indexed view, unless the view contains an expensive logic (aggregations or joins) that you don't want to perform each time you query the view. Please note that even when the view is "materialized", the optimizer is free to ...
As mentioned in this answer, "REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock" on the table. Following the crumb trail to documentation we can read that an EXCLUSIVE lock on a table "allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed". In the same paragraph we can see that "EXCLUSIVE conflicts with ... ...
Materialized Views Do Not Exist in MySQL.
Flexviews has been recommended in the DBA StackExchange before
Bill Karwin's post : Does MySQL have a version of Change Data Capture?
Redguy's post : Need some support on MySQL Query
Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you haven't ...
Turns out this wasn't as complicated as I thought! (With just a little knowledge of pg_catalog...)
Part 1: Query whether a materialized view exists:
SELECT count(*) > 0
FROM pg_catalog.pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'm'
AND n.nspname = 'some_schema'
AND c.relname = 'some_mat_view';
Nice and easy.
Part 2: ...
Unless a NOEXPAND hint is used, SQL Server always expands the view reference to the underlying stored query before optimization begins.
It may later chose to match part(s) of, or the whole plan, back to one or more indexed views later on in the optimization process.
There are two common reasons that the original expansion isn't reversed later:
The plan ...
From the documentation:
A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access ...
It seems to ignore any index I put on it
Unless you're using SQL Server Enterprise Edition (or equivalently, Trial and Developer), you will need to use WITH (NOEXPAND) on the view reference in order to use it. In fact, even if you are using Enterprise, there are good reasons to use that hint.
Without the hint, the query optimizer (in Enterprise Edition) may ...
There is no INCREMENTAL, it's very simple.. From the docs
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]
Perhaps you're confusing it with CONCURRENTLY which is about concurrency (locking) and not minimizing updates.
Refresh the materialized view without locking out concurrent selects on the materialized view. Without this ...
You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) in order for your query to use the Indexed View (instead of expanding it to the underlying table). This will solve your first issue.
This is how you can use it in your query:
SELECT TOP (100) *
FROM Losses_CombinedPortfolio ...
Short answer: You can't.
There's a few things going on here.
First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". Your provided example, in fact, could be handled without the nested XML generation.
Let's make some test tables and data:
DROP TABLE IF EXISTS T1;
As a_horse_with_no_name said in a comment:
No, that's not possible. You need some kind of scheduler that runs refresh materialized view e.g. pg_cron or something on the operating system level – a_horse_with_no_name
Alternatively, if you need a MATERIALIZED VIEW that refreshes when you run SELECT, just remove MATERIALIZED and use a regular VIEW. ...
You can prevent altering an indexed view with a DDL trigger. But the implementation is a bit complex, because the DDL trigger runs after the view has been altered and the index dropped, but before the change has been committed, and you can't directly detect that the view previously had an index.
So you have to get up to some shenanigans with extended ...
As someone said in the comments, pg_stat_statements is the way to get the statistics. Put this into your postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
Then run this query:
CREATE EXTENSION pg_stat_statements;
After that, this sample query (copied from the docs linked above)...
There's nothing you can do about this. SQL Server automatically takes steps to ensure the indexed view always stays synchronized with the base tables.
When reading the indexed view to see if the data associated with the changed key(s) exists or not, SQL Server needs to ensure that data does not change until the view maintenance is complete. This includes ...
REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock, so SELECTs may still run. Per the manual.
There seems to be an oversight with REFRESH MATERIALIZED VIEW (without CONCURRENTLY) as it's not listed there. A search of the source code shows that ExecRefreshMatView take an ACCESS EXCLUSIVE lock, as you might expect, so no other queries may run on ...