Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [materialized-view]

Defined like a view but holding persistent data like a table, Materialized Views are a feature of a number of RDBMSs including Oracle, DB2 and postgres. SQL Server has a similar feature called an Indexed View that is considered similar enough to come under this tag.

0
votes
2answers
34 views

Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL?

Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN Can somebody clear this up for me? What's ...
0
votes
1answer
18 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
0
votes
1answer
54 views

oracle-mysql fast refresh materialized view

Is is possible to Create Fast refresh enabled Materialized view from MySQL to Oracle . i know steps for oracle to oracle Fast refresh enabled Materialized view. But now the scenario is need to ...
0
votes
0answers
16 views

Refreshing portion of materialized view in Postgres [duplicate]

I'm new to Postgres. Is there a way to refresh a portion of the data with in a materialized view? I'm thinking it would be beneficial for my application over a traditional view, since there will ...
1
vote
0answers
46 views

What are the advantages of using a Materialized View directly on a table?

Recently, in enterprise's Oracle database, I have seen some materialized views that filter directly columns of table without adding aggregate fields and without calling custom functions. Example: ...
0
votes
1answer
31 views

Is using indexed view with no expand on standard edition a must?

I have recently read the text below in 70-762 Developing SQL Databases (p. 67): The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the ...
-1
votes
0answers
48 views

Impdp error in MATERIALIZED_VIEW

i had take a structure backup on database. expdp works perfect. but while impdp i am getting error in mview to get ddl of Mview. expdp :- expdp abc/xyz full=Y CONTENT=METADATA_ONLY directory=di1 ...
1
vote
1answer
44 views

Creating materialized view is very slow

I have quite a complex query in PostgreSQL 10.4 on Amazon RDS (3 joins, quite a few conditions). It takes from 1.5 to 6 seconds to perform a select. However, when I want to create a materialized view ...
1
vote
1answer
44 views

What is the proper procedure for fixing materialized view replication?

Oracle 10 Standard: I have a number of materialized views built on pre-built tables. These are fast refreshed from a remote database over a link from one master db. They've generally been working ...
0
votes
1answer
81 views

Create a materialized view on commit with PIVOT function

I'm trying to create a materialized view with on commit refresh while using a PIVOT on it's select. I have the following tables: Events: represents an event that the system process. It's like a ...
3
votes
0answers
45 views

Bloat on Materialized Views

The Postgres documentation says that materialized views with concurrent refreshes generate bloat as usual tables, because it executes deletes and inserts. For usual tables, there are some queries to ...
0
votes
1answer
83 views

Export Materialized View from postgresql as a table?

I have a materialized view I created in Postgresql. I was wondering is there a way to pg_dump the materialized view as if they were tables, so when I import them into another database it sees it as ...
0
votes
1answer
42 views

Create or refresh materialized view

How would you create new or otherwise refresh a materialized view in Postgres? I've tried something like this: CREATE OR REFRESH MATERIALIZED VIEW institution_mv AS SELECT entity_id, ...
1
vote
0answers
27 views

How to aggregate transactions to form a stateful view which can be updated efficiently?

How to aggregate transactions to form a stateful view which can be updated efficiently? For example how to turn the following transactions: CREATE TABLE txns_combined(from_party,to_party,price,qty,...
0
votes
1answer
127 views

Postgres 9.5 Materialized View from a table with jsonb columns

For the purpose of improving performance of queries on a rather large table (5M+ rows) containing jsonb columns, I would like to create materialized views with data derived from those columns. ...
1
vote
0answers
833 views

How to import materialized view in oracle using impdp [closed]

Is there any way to import a materialized view in Oracle using impdp? I have created a dump with full =y, but when I import the dump using impdp it fails to import the materialized view. In the expdp ...
4
votes
1answer
76 views

Convert columns into rows for use with indexed/materalized view

I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. ...
0
votes
1answer
41 views

SQL Server - materialised (indexed) view not working as expected

I've created a materialised view in SQL Server, but when selecting from it I get the same performances and the same execution plan than when I select from the same query not "materialised". The view ...
0
votes
0answers
211 views

Autorefresh for Postgres materialised view?

Is it possible to set an automatic refresh frequency for materialized views? Here's the situation, I have an important but relatively small (20 thousand row) table that's subject to constant updates (...
4
votes
1answer
162 views

What is a good way to parameterize a materialized view on Postgres?

Given for example a materialized view like this (Postgres 10.3): create materialized view my_view as select * from my_table where sell_date < '2018-03-01'; The sell_date comparison value ('...
0
votes
0answers
40 views

In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't

I am using SQL Server 2016-SP1-CU8 Here is the code to setup my scenario: --1. Drop indexed views (in case of re-running IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1 IF ...
0
votes
0answers
64 views

get_ddl Returns ENABLE ON QUERY COMPUTATION for 11g Materialized Views

I am hoping someone can help us solve an issue that is causing errors in 12c (12.2.0.1.0) during our regression testing. We have recently migrated from 11g to 12c so that none of our materialized ...
0
votes
1answer
62 views

SQL Indexed View for Datamart Reporting with Transactional Replication

Should Indexed Views be utilized/recommended as method to auto-generate DataMart Reporting tables? We have a Customer timeline of tables which can be simply joined on CustomerId in each tables. ...
0
votes
0answers
75 views

The clause 'WITH' of materialized views should coincide with clause WITH materialized view logs?

I am trying to optimize a materialized view and I note that clause WITH in the materialized view is defined as WITH PRIMARY KEY and the same clause in the materialized view logs is defined as WITH ...
0
votes
0answers
21 views

Refresh several materialized views with serial propagation

I am trying to optimize the refreshing of several materialized views in Oracle. I have 4 materialized views refreshing on demand every 2 hours and I have a job for each one of them. DBMS_SNAPSHOT....
0
votes
0answers
30 views

In 2018, what are the available ways of getting “materialized views” out of a mySQL DB? [duplicate]

I got a MySQL DB i'm using as data warehouse to store normalized data. For report building out of it, I think I should de-normalize my data, and then cache it somewhere with sort of a refresh tool (...
0
votes
0answers
27 views

Oracle: How do I extend a materialized view transactionally?

Is there a way in Oracle 12 for me to recreate a materialized view with additional columns without impacting any of the services using the view?
1
vote
2answers
498 views

Postgres Materialized View Dramatically Increasing In Size

I have a production postgres 10 database. Every hour I run "refresh materialized view concurrently" on my materialized view to recalculate the values; only a few new rows get added each day. All the ...
1
vote
1answer
48 views

PostgreSQL drop view in case of unknown whether materialized or not

I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW. DROP VIEW IF EXISTS myview; throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I ...
7
votes
3answers
1k views

What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?

According to the docs: CONCURRENTLY Refresh the materialized view without locking out concurrent selects on the materialized view. (...) ... OTHER CONTENTS ... Even with this option ...
0
votes
1answer
369 views

Oracle - Foreign Key on non Unique - is materialized view the answer?

we have the following table to store texts in different languages: CREATE TABLE TXT ( TXTID VARCHAR2(10 BYTE) NOT NULL , LANGUAGE VARCHAR2(2 BYTE) DEFAULT 'DE' NOT NULL , STEXT VARCHAR2(10 BYTE) ...
1
vote
0answers
37 views

Combining two event tables into one postgres view

I have two tables, events and event_logs. The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime. Event +-----...
0
votes
1answer
41 views

Multiple table constraint in oracle

A table of boxes, with a capacity column, and a table of items with columns box and size, and you want to enforce the constraint that an item can't be put in a box of capacity smaller than the item. ...
1
vote
0answers
71 views

Error creating aggregate materialized view

Trying to create an aggregate materialized view: CREATE MATERIALIZED VIEW MV_LLATTRDATA_MAX_VERSIONS NOLOGGING BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS ...
0
votes
1answer
69 views

FK to table with non-unique values via a on commit refreshing materialized view?

I would like to create a FK to a table without unique values. So I cannot create a simple FK. [to clarify, the parent table has duplicates - dont ask why, not in my control ] I tried creating a ...
3
votes
2answers
796 views

Can't create indexed view against table with masked columns

I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view. create ...
1
vote
3answers
4k views

Materialized view became INVALID and USER_MVIEWS.COMPILE_STATE becomes 'NEEDS_COMPILE'

I have an application which is getting data from Oracle(11.2.0.4) Materialized view, there is expected behaviour about materialized view will go INVALID when any DML action happens on the associated ...
3
votes
1answer
2k views

How to do an incremental refresh of a PostgreSQL Materialized View?

I'm looking at the PostgreSQL docs where: REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded. However, since 9.5 I believe, there is ...
3
votes
1answer
689 views

Analyze needed after a refresh materialized view?

Are we supposed to run ANALYZE myview; on a PostgreSQL 9.6+ materialized view after running a REFRESH MATERIALIZED VIEW CONCURRENTLY myview;? Or is it useless (maybe index statistics are already ...
0
votes
2answers
2k views

Trigger to update a materialized view once after relevant transaction?

I'm using PostgreSQL 9.6. I have a materialized view to support full-text search across two tables, which I'll call posts and tags. The tables are infrequently updated and frequently searched. I'm ...
1
vote
1answer
602 views

Refresh materialize View fast on commit multiple table

In a materialized view with refresh fast on commit i have the below query select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID, pp.hierarchicode,pp.id, aup.userid from app_useracess aup,...
0
votes
1answer
71 views

Why would Indexed View perform much worse than equivalent table?

I have this table create table SkaData.FiscalPeriod( PeriodNo int not null constraint FiscalPeriod_FK_BasePeriod references SkaData.Period(Number) ,YearEndMonthNo ...
35
votes
1answer
3k views

How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?

SQL Server 2017 has a couple new stored procedures: sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261), @rgCode int sp_refresh_snapshot_views – input param for @rgCode int ...
1
vote
1answer
487 views

Nologging materialized view

I need to create a materialized view (MV) with auto refresh every hour. After I create it, a lot of redo logs are generated (10GB per hour). I think the solution is to recreate the MV in NOLOGGING ...
3
votes
1answer
897 views

How to create a TSVECTOR column in a materialized view?

I am trying to implement full text search in a materialized view (MV for brevity), and I read that it's not possible to alter the MV itself, which rules out these solutions: Adding a new column with ...
-3
votes
1answer
129 views

Optimizing an indexed view used for selects to also do updates

I have a materialized indexed view that returns ~2.6 million of the ~2.7 million rows in the table, causing the optimizer to mostly use clustered index scans. If I do an update on this view will it ...
1
vote
1answer
83 views

Why is an index I created not being used in a query execution?

Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be. I have a query inside an indexed view, like this: SELECT [a].[...
4
votes
1answer
352 views

Indexed View over multiple partitioned tables and ALTER TABLE SWITCH

Is it possible to have an indexed view that combines 2 partitioned tables and still use ALTER TABLE SWITCH to move data out of both base tables? I'm receiving the following error when trying this and ...
0
votes
0answers
261 views

Writeable Oracle materialized view without FAST REFRESH requirements

I have an ON DEMAND materialized view in Oracle 11g-r2. The query for the m-view is complex and has nested dependencies that include other m-views. AFAIK the materialized view logs required for FAST ...
3
votes
1answer
530 views

Refresh Materialized View impact on the DB

Hi we are running an PostgreSQL 9.6 database in Amazon RDS under a m4.large(2cpu 8gb) and a provisioned IOPS of 1000. The use case is the following: We have a table with several million of registries (...