I don’t think MySQL supports materialized views which is what you would need, but it wouldn’t help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would … Read more
I don’t think there is anything built in the system that provides this as of 9.3.4. When I need to provide the date of last refresh I add a column called ‘last_refresh’ to the select query in the materialized view since data in the materialized view won’t change until it is refreshed. I also prefer … Read more
Pure SQL Show all: SELECT oid::regclass::text FROM pg_class WHERE relkind = ‘m’; Names are automatically double-quoted and schema-qualified where needed according to your current search_path in the cast from regclass to text. In the system catalog pg_class materialized views are tagged with relkind = ‘m’. The manual: m = materialized view To drop all, you … Read more
Run this script to refresh data in materialized view: BEGIN DBMS_SNAPSHOT.REFRESH(‘Name here’); END;
Dynamic query rewriting. Materialized views define not only relationships, but also allow you to precompute expensive joins and aggregations. The optimizer is smart enough to use the MV to fetch relevant data even if the MV isn’t explicitly used in the query (given DB settings, etc). Your question was tagged as Oracle, but MSSQL also … Read more
You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2. create or replace function refresh_mat_view() returns trigger language plpgsql as $$ begin refresh materialized view mat_view; return null; end $$; create trigger refresh_mat_view after insert or update or delete or truncate on table1 for each statement execute procedure refresh_mat_view(); … Read more
I’ll need to invoke REFRESH MATERIALIZED VIEW on each change to the tables involved, right? Yes, PostgreSQL by itself will never call it automatically, you need to do it some way. How should I go about doing this? Many ways to achieve this. Before giving some examples, keep in mind that REFRESH MATERIALIZED VIEW command … Read more
Materialized views are disk based and are updated periodically based upon the query definition. Views are virtual only and run the query definition each time they are accessed.