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.

learn more… | top users | synonyms (1)

3
votes
1answer
29 views

Query the definition of a materialized view in Postgres

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view: SELECT * FROM ...
-2
votes
0answers
30 views

Creating Mview in Oracle is throwing error

I keep on getting error when I try to create a fast refresh materialized view. Not sure what is the cause of getting the error. ---PREBUILT TABLE--- Create table k_dim_mvw( id NUMBER(10), time ...
1
vote
1answer
18 views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
0
votes
1answer
24 views

How to view the original script that built a materialized view and that was created by another user?

In the Oracle database server use, another user (called MIMIC2DEVEL) created a materialized view and made it public. How to view the original script that was used to built it? Here are the grants on ...
1
vote
0answers
25 views

Create Nested XML in a Sql Server Indexed (Materialized) View

This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this: SELECT Col1, Col2, ...
2
votes
1answer
50 views

Postgres: check disk space taken by materialized view?

I know how to check the size of indexes and tables in Postgres (I'm using version 9.4): SELECT relname AS objectname, relkind AS objecttype, reltuples AS "#entries", ...
0
votes
0answers
13 views

Refresh materialized view in Teiid

I have created a teiid vdb with sql server data source. The view model contains a materialized view, how to refresh the materialized view manually or automatically? Is there any sql command to trigger ...
0
votes
1answer
61 views

Working with Materialized View

I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4. When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH ...
3
votes
1answer
120 views

Is it possible to backup and restore a materialized view with it's data?

I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing. Is it possible in PostgreSQL 9.4? (the alternative ...
0
votes
0answers
23 views

Oracle Mview - Get DML Type

I'm trying to create a replication of a table on a remote server. Table on remote Server : tbl_remote. i created a MATERIALIZED VIEW LOG on tbl_remote. also in my local server i created a ...
0
votes
0answers
50 views

index of aggregate value with fast update?

Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire ...
4
votes
1answer
192 views

How to optimize: having COUNT(*)>1

Without considering the indexes, Is there a way to re-write this query and get the same result quicker? select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*) from Facts.FactBackOrder fo group by fo.OrderNo, ...
1
vote
0answers
59 views

When will SQL Server automatically use a supporting indexed view?

I have the following expensive query that runs against our data warehouse: SELECT d.DateYear, COUNT_BIG(*) AS WidgetCount FROM Widgets AS w JOIN Dates AS d ON d.DateId = w.DateId WHERE ...
6
votes
2answers
375 views

Using indexed views for aggregates - too good to be true?

We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g. SELECT ...
1
vote
1answer
846 views

Best way to create materialized view in mysql

I am using mysql 5.6, I am not being able to create materialized view here like oracle. I got one or two solution like flexview and all... Can anybody tell me best way to create materialized view in ...
2
votes
2answers
872 views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...
1
vote
1answer
76 views

Indexed View - how to verify if it is worthy?

I have the following indexed view: CREATE view VProcessStatus WITH SCHEMABINDING AS select processstatus, StatusCount = COUNT_BIG(*) FROM DBO.tbltaxtransaction GROUP BY ...
0
votes
0answers
30 views

Is it possible to create materialized view based on another materialized view in postgres?

I have a very large table and I summarize it in a materialized view. I then create an even smaller materialized view based on the earlier mview. The second mv is always showing zero rows. Is there a ...
1
vote
1answer
284 views

postgres materialized views and indexes to improve aggregate performance

I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system. Obviously when I ...
1
vote
1answer
73 views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
1
vote
0answers
46 views

reduce the cost of maintaining a materialized view? [closed]

how to reduce the cost of maintenance a materialized views? and when I selected between maintaining by recompiling and maintaining by refresh?
2
votes
0answers
48 views

Optimization suggestions for db2 materialized view

I have a view for db2 9.7 which has consolidated o/p from few tables. The view is built and it is working fine I want optimization suggestions on the view SQL CREATE VIEW XYZZ AS SELECT ...
0
votes
0answers
162 views

How to find materialized view log dependencies over database links

Is there a way to identify materialized views that have used a given MV log, even if the MV is using the log over database link? (Oracle EE 11.2) The problem is that I would like to drop a certain MV ...
0
votes
1answer
73 views

The materialized view is already optimal and cannot be tuned any further

I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error: Error report: ORA-13600: error encountered in Advisor QSM-03116: The ...
5
votes
1answer
150 views

Why does dropping a MVIEW trigger a full refresh?

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again. The MVIEW was created with a statement like this: create materialized view ...
2
votes
1answer
226 views

How to move 200 materialized views without having to re-create in the new home

I am moving a database with 200 materialized views into a new server. All of them are 'fast refresh' materialized views , means they have corresponding materialized view logs, in this case sitting on ...
0
votes
0answers
78 views

Simple full text search on 5000 rows is very slow

I am running this full text search on an indexed view and it is taking 20 seconds to query across 5000 rows and return 0 rows. I believe it has something to with the forward slashes because all other ...
10
votes
1answer
465 views

Resolving deadlock from 2 tables only related through indexed view

I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it. This is on a production environment running SQL Server ...
0
votes
0answers
149 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
1
vote
0answers
156 views

Refreshing Materialized view creates many child cursors

Doing some tests, I noticed that having REFRESH FAST ON COMMIT materialized view and flashbacks enabled cause Oracle to create many child cursors in some cases. For example, CREATE TABLE table1 ...
6
votes
2answers
2k views

Why do Indexed Views not permit non-unique clustered indexes?

I've been looking into using Indexed Views to increase performance on a few of our most commonly used views. However Indexed Views do not support non-unique clustered indexes which goes a little ...
7
votes
2answers
94 views

Need to include FKs in SQL Indexed View clustered index?

I'm using a SQL Server Indexed view to improve performance of a join that filters a (very) large table by a category on a small table. eg: CREATE VIEW BigTableSubset WITH SCHEMABINDING AS SELECT ...
5
votes
1answer
349 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
4
votes
1answer
2k views

Materialized view: compilation_error

When executing the following code on the SCOTT schema I receive a compilation_error. What am I doing wrong here? I doesn't even tell me what the error is. SQL> create materialized view dept_emp_mv ...
6
votes
0answers
278 views

Dropped index from view still referenced in execution plans [closed]

On a SQL Server 2005 Enterprise edition server, an index has been dropped from a view. When running a select * query that includes this view, no results are shown. In addition, the execution plan ...
1
vote
1answer
258 views

One Materialized View in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
3
votes
1answer
378 views

Index will be removed

I'm trying to simply change the length of a column within a table through SQL Server Management Studio, but receive a 'Validation Warning': 'MyTable' table -Warning: The following schema-bound ...
11
votes
2answers
912 views

DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

TL;DR: I've got an unfixable corruption in an indexed view. Here the details: Running DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS on one of my ...
2
votes
1answer
737 views

Creating Indexed View GROUP BY Epoch Date

I have a few big tables with about 6 billion rows that I was looking to optimize. Clustered key is Epoch (unix date time which is the number of seconds that has passed after 1970) and customer ID. ...
2
votes
1answer
260 views

Updating column in underlying table causes massive transaction log growth

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
1
vote
1answer
1k views

Immediate refresh materialized views (MQT) with OUTER JOIN under DB2

DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE). I cannot find a simple solution to get round this problem. Perhaps with ...
1
vote
2answers
429 views

Database Design question, calculate or save financial positions

As the question states i'm currently internally strugling witha database design question. I work as a software developer (and i tend to do everything database related aswell since we're rather small) ...
3
votes
1answer
679 views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
4
votes
1answer
218 views

Materialized View PostgreSQL

Is there any possibility of having built-in materialized views in PostgreSQL ? I don't want Gardner version or somehthing like physical table populated by triggers. I've read that was/is something ...
0
votes
0answers
164 views

Materialized view for a yet-to-be-defined DBlink

In our product we have a main application with an oracle 11g DB and an optional module with another DB. We want the two to communicate through a dblink and I was told to put some code for the ...
4
votes
1answer
367 views

Adding indices to views

I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table. ...
3
votes
1answer
1k views

How much of a view is persisted when you create an index?

Say I have a view defined by the following SQL: SELECT t1.id, t2.name, t3.address FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.id = t2.tID INNER JOIN Table3 t3 ON t1.id = t3.tID From what I ...
4
votes
2answers
741 views

Materialized view for the last 24 hour period

I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS ...
10
votes
2answers
2k views

Risks of changing to ARITHABORT ON

I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion ...
3
votes
1answer
437 views

Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?

My initial question was How does someone determine if it is a good idea to index a view?. Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, ...