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.

Filter by
Sorted by
Tagged with
4
votes
1answer
225 views

How can I specify the DDL of the index that is implicitly created when creating a materialized view log?

When I create a materialized view log, Oracle implicitly creates an index on that log. For example, when creating a materialized view log on table foo, Oracle creates the view log table mlog$_foo and ...
4
votes
1answer
2k views

Indexed View Slows Replication

I have setup SQL Server transactional replication, and then added indexed views at the subscriber, but the indexed views appear to really slow down the subscriber and almost bringing it to a halt. I ...
4
votes
1answer
3k views

Snapshot too old from materialized view refresh job

This Oracle database has a DBMS_JOB that runs the following materialized view refresh script : dbms_refresh.refresh(ABCD.V_POSTAL_CODES); If I run it by hand, it's finebut if the job executes it a ...
4
votes
1answer
865 views

Issues with Indexed Views in SQL Server 2008r2 on a table with persisted columns and with low DML activity

I am issues with using indexed views. I have a product catalog and a stored proc reads the data in the rows according to various filters on the data. At the same time, the data needs to remain in XML ...
4
votes
1answer
297 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. ...
4
votes
1answer
1k 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, ...
3
votes
2answers
4k views

PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

[I think the root cause of this problem is me not understanding permissions and privileges...] So, to set the stage, the set up I have is a DB, call it MyDb. I have two users, spu1 and u1, spu1 is a ...
3
votes
1answer
555 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, ...
3
votes
2answers
1k 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 ...
3
votes
1answer
2k 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. ...
3
votes
1answer
149 views

Do operations on indexed views happen in parallel?

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel. Is ...
3
votes
2answers
14k views

WITH using INDEX hint in Oracle

I have a fairly complicated query. I have written two different query to accomplish my goal. First one is using WITH, other one is using global temporary tables. First One: WITH A ( KNO .. ) , ...
3
votes
1answer
7k 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 ...
3
votes
1answer
1k 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 ...
3
votes
1answer
546 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, ...
3
votes
1answer
2k 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
2answers
4k views

Oracle 12c - Create materialized view results in ORA-00942

I have a view that I can access through a database link as such: SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB; COUNT(*) -------- 110 I can create and successfully query the ...
3
votes
1answer
627 views

Oracle Materialized View

I need your help with a issue on Oracle Materialized View. I created the Materialized View using the following command: CREATE MATERIALIZED VIEW SNAP_TEST TABLESPACE tbs NOCACHE LOGGING NOPARALLEL ...
3
votes
1answer
832 views

Renaming a column in a materialized view with an unknown name

I have a materialized view with an unknown column name, and I need to give that column a name. I can't just recreate the view because it took a long time to create. dba=# \d temp_name_relationships; ...
3
votes
1answer
113 views

Most efficient way of caching queries / processed data

I have doubts on database design. I'm trying to know if there is a way to increase query performance, and the easiest way for me to explain my doubt is with a small example. I have a news website ...
3
votes
1answer
850 views

What indexed view capabilities does Azure SQL Database have?

Does Azure SQL Database support indexed views? Does it support automatic indexed view matching without NOEXPAND? Does the answer depend on the pricing tier? I was unable to find definitive answers to ...
3
votes
1answer
999 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 ...
3
votes
1answer
313 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
3
votes
2answers
4k views

Error updating a master table of a materialized view

I've created several materialized views, and all but one are working correctly. The specific error I'm getting (via a Java job) is this: error loading from table 'STG_INTERACTION_RESOURCE_FACT' to ...
3
votes
0answers
102 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 ...
3
votes
0answers
461 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...
2
votes
2answers
4k views

Materialized View - Oracle

I have materialized view for which i want to alter its refresh time: REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE I want to know few things in this. What does NEXT ...
2
votes
2answers
1k 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 ...
2
votes
2answers
74 views

How to make Indexed Views work for SQLCLR?

In SQL Server, the indexed view is a hellscape of limitations. But I need one. I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search ...
2
votes
3answers
1k views

A performance Issue when querying a big table using a PK/clustered index

To simplify the situation, I will only consider one large table... On a nightly basis a store will send all the new and changed data for one day for a large table to the head office. (this portion is ...
2
votes
1answer
151 views

T-SQL to check whether or not a indexed view can be created on tableX?

After applying my database changes on the test environment, the test team starts testing their processes, one of the things I get is a truncate table error because the table is used in an indexed ...
2
votes
1answer
141 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS /*-------------------------------------------------------...
2
votes
1answer
167 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 ...
2
votes
1answer
12k views

Materialized view taking too much time

i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in ...
2
votes
1answer
5k views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
2
votes
1answer
2k views

Create Materialized View from foreign table

I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server. What do I have to add to make the query access the foreign ...
2
votes
1answer
2k 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 ...
2
votes
1answer
317 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 ...
2
votes
1answer
2k views

Creating Bitmap Indexes on a Materialized View

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned: Also, remember that bitmap indexes are only suitable for static ...
2
votes
2answers
3k views

Materialized views use older dbms_job instead of scheduler?

Very new to this topic - using 11g. When I create a materialized view, a matching job (not a scheduler job) is created. But it's my understanding that dbms_job is being replaced by dbms_scheduler. ...
2
votes
1answer
332 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 ...
2
votes
0answers
408 views

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Version 9.3) Asking this after searching around without any solution: I create a simple materialized view (in practice, I have a few of them). I call pg_dump to backup the database. While pg_dump ...
2
votes
0answers
203 views

Shouldn't DISCARD PLANS re-evaluate every function definition in postgres?

Hi have a set of functions that take a row of a table (let's call it bookings) as input parameters. Some of these functions call other functions, and they are used to calculate a materialized view. ...
2
votes
0answers
2k views

Postgres: How to log materialized view refresh

I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it ...
2
votes
0answers
506 views

parallel pg_restore with materialized views

I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS. To troubleshoot the restore process, I have split the restore ...
2
votes
0answers
156 views

Promote Postgres standby while materialized view updates in primary

I have one primary Postgres server (serverA) and one standby Postgres server (serverB), both running Postgres 9.4 with Streaming Replication enabled. Is it possible to update a materialized view on ...
2
votes
1answer
127 views

Implications of many materialized views in Postgres?

I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be ...
2
votes
0answers
404 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 (...
1
vote
3answers
9k 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 ...
1
vote
1answer
1k views

Unique clustered index on indexed view fails because of aggregates

I want to create an indexed view similar to this (slightly simplified): Create VIEW dbo._v1 WITH SCHEMABINDING AS SELECT COUNT_BIG(*) as CB ,[ID] ,TITLE] ,dbo....