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
0
votes
1answer
80 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. ...
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 ...
1
vote
1answer
137 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 ...
12
votes
3answers
4k 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
702 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
55 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
48 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
139 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
110 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 ...
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 ...
1
vote
3answers
11k 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 ...
6
votes
1answer
4k 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 ...
5
votes
1answer
2k 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 ...
1
vote
2answers
6k 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
2k 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
81 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 ...
36
votes
1answer
4k 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
1k 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
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
1answer
213 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
113 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
634 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 ...
5
votes
1answer
1k 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 (...
2
votes
3answers
2k 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 ...
10
votes
1answer
513 views

Why does the READPAST hint cause indexed views to be ignored?

I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. It seemed like a good way to go because financial transaction records are only ever ...
1
vote
1answer
5k views

Using NEXT clause to set periodic materilized view refresh in oracle and verifying refresh

I am not able to find the right documentation on setting specific timings for materialized view refresh for oracle. I read up the documentation and some examples there, however there is no definite ...
1
vote
1answer
569 views

Does a materialize view refresh in its creation?

I create a materialized view with the following cast function. It does not give me an error during creation although it exists an error. But refresh gives the error. CREATE MATERIALIZED VIEW ...
2
votes
0answers
487 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 ...
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 ...
0
votes
1answer
1k views

Materialized View for SQL Server

I want to know if there is some thing similar to materialized view for Oracle in SQL Server . Any ideas ?
10
votes
1answer
10k views

Refresh a PostgreSQL materialized view automatically without using triggers

I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this ...
5
votes
1answer
526 views

Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table

First, sorry for the long title. The setup I reduced the example to the minimum, to make it clearer, so there is no meaningful semantic anymore. DBMS: Azure SQL Database V12. Assume there is ...
3
votes
0answers
355 views

An Indexed View slows updates to a crawl. Is there a hint to work around it?

I experimented with creating an Indexed View. I finally had a case which (almost) met the criteria for one. All inner joins, very few non-deterministic functions (which were moved to an outer view) ...
1
vote
3answers
20k views

How to know when a refresh of a materialized view has been completed

After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query ...
1
vote
1answer
257 views

Create FAST Materialised View in Oracle 10g with ROWID

I want to create FAST refresh the below Materialized view through rowID method. `CREATE MATERIALIZED VIEW XXARG_ACC_ANALYSIS REFRESH FAST ON DEMAND AS SELECT gjb.name "Batch_Name", gjs....
0
votes
1answer
3k views

Creating a Materialized view with fast refresh and on Demand option

create materialized view XXARG_GOKU AS select XDR.GLOBAL_ID XDR.PARTY_ID, XDR.EMAIL_ADDRESS, XDU.CITY_CODE, XDU.STD_CODE, XDU.PIN_CODE_SERIAL_NO from XXARG_DUMMY XDU, XXARG_DRAGON XDR where XDR....
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 ...
1
vote
1answer
513 views

Postgres: Database architecture and materialized views

I have a database with a huge table, that gathers the ranking history of mobile applications. The table is quite big, around 120 Go. In order for my DB queries not to be too slow, I implemented ...
0
votes
1answer
224 views

Inheritance and performance in database [closed]

I have 3 table linked by an "inheritance" with Entity Framework. Sadly, I have poor performance with those query because I can't make any good index. Here is the tables : CREATE TABLE [dbo].[...
1
vote
1answer
2k views

Changing the WHAT column of DBA_JOBS

I am using a job to update a materialized view. I found that the job would lock itself if the exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"''); was not committed and ...
-1
votes
1answer
2k views

Create materialized view no refresh from existing view with index

I've already got a working db view : CREATE OR REPLACE FORCE VIEW "USER_DATA_VIEW" ("APP_USER_ID", "FIRST_NAME", "LAST_NAME", "FULL_NAME", "LOCATION_ID", "LOCATION_NAME") AS SELECT U.APP_USER_ID, ...
3
votes
2answers
5k 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 ...
2
votes
1answer
155 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 ...
1
vote
1answer
110 views

Multiple Non-Indexed Views with INCLUDEs vs. Multiple Indexed Views in High Write Situations

When you want to encapsulate your T-SQL to select different subsets of data from the same base tables, is it more efficient to use plain views in conjunction with nonclustered indexes and INCLUDEs on ...
1
vote
0answers
442 views

Multiple UNIONs get slow. Indexing or materialized views?

Let me start by stating that my DBMS is PostgreSQL 9.4. I have a database with, say 30 tables. Each table has a mirror table with historical data, that is the same columns and some timestamps. To ...
0
votes
2answers
248 views

Database Design: Should I use a Materialized View

I am designing a database based off of the NBA stats website (http://stats.nba.com/player/#!/201939/?p=stephen-curry) If you go to the link posted on the player's stat, you will notice that the ...
1
vote
0answers
951 views

Oracle 12c fast refreshing nested materialized view problems

I have a couple of nested materialized views that fast refresh. They worked fine in Oracle 11g but since upgrading to Oracle 12c (12.1.0.2.0 on Solaris) I am occasionally getting duplicate rows (the ...
3
votes
2answers
928 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 ...
0
votes
1answer
258 views

On a single table, how is an indexed view different from a non clustered index?

Assuming that no joins are involved, and the PK of the view (the UNIQUE Clustered Index) is the same as that of the base table, is there a difference between how a nonclustered index (NCL) and an ...
2
votes
0answers
235 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. ...