The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
1
vote
1answer
322 views

Group by's in materialized views and query planning [duplicate]

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table. The query is something like SELECT COUNT(*) FROM BaseTable WHERE Slot ...
1
vote
1answer
4k 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
2answers
633 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 ...
1
vote
1answer
41 views

Data Replication in Oracle

I have a table on master site which only allow insert operation. I want to replicate the rows which are inserted recently so, need something which can track the last record replicated into the local ...
1
vote
1answer
34 views

Different results from select and from materialized view

select distinct on (p) coalesce(native_fcm_token, push_token) p from session where user_id = 2779 and ((push_token is not null) or (native_fcm_token is not null)) returns 27 rows refresh ...
1
vote
2answers
5k 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
504 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 ...
1
vote
1answer
1k views

pgAdmin crashes or freezes when big MATERIALIZED VIEWs are refreshing

I'm using pgAdmin III v1.22.1 on Windows 7 to connect to a PostgreSQL server version 9.4.8 on Red Hat. We have scripts to REFRESH MATERIALIZED VIEWS during the night. When they are refreshing, I can'...
1
vote
1answer
86 views

Indexed Complex View

The aggregate functions MIN,MAX,AVG, and STDEV are not allowed in an Indexed View in SQL Server (Create Indexed Views - MSDN Library). Does SQL Server have a mechanism to index the results of the ...
1
vote
2answers
1k views

Database Design question, calculate or save financial positions

As the question states I'm currently internally strugling with a database design question. I work as a software developer (and I tend to do everything database related as well since we're rather small)...
1
vote
1answer
115 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 ...
1
vote
1answer
448 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 ...
1
vote
1answer
101 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
1answer
274 views

Include scripts for Indexes on Alter Views

After a Developer altered an Indexed View to add a comment, the clustered index that was created on it was dropped with no warning. This caused a failure as there are a couple of procedures that have ...
1
vote
2answers
113 views

Is there a way to do the following query with joins instead of a sub query?

I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view. The only thing similar to my problem is the greatest-n-per-group queries but ...
1
vote
1answer
193 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 ...
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,...
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 ...
1
vote
1answer
111 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].[...
1
vote
1answer
237 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....
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
vote
1answer
1k views

PostgreSQL conditional join and value distribution with materialized view

So I essentially have two tables in a database. One which is the base table of all of our media data, and another that has potential override data for spend. Not everything in the display table will ...
1
vote
1answer
501 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 ...
1
vote
1answer
3k 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
1answer
3k views

How to drop materialized view logs if underlying log table ($MLOG_…) is dropped?

In an attempt to work around another problem, I have mistakenly dropped the underlying tables for a materialized view log, and now I can neither drop that log nor the MV relating to it. Our DBA says ...
1
vote
0answers
24 views

MSSQL - Lock escalation on partitioned indexed view

I'm using partitioned tables where I set LOCK_ESCALATION to AUTO to prevent lock escalation to table level, but keep it on partition level. On the top of that table I have indexed view which is ...
1
vote
1answer
32 views

Oracle Materialized View - Is it Safe to Use Only ROWID as Record Identifier?

this is a question related to Oracle 12c. I'm currently trying to create MVs as data sources for Golden Gate replication. We need fast refresh because We have to replicate 60,000 rows + at one time ...
1
vote
0answers
86 views

Do Hive Materialized Views respond to added partitions to source tables?

Hive Materialized Views support partitions, CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name [DISABLE REWRITE] [COMMENT materialized_view_comment] [PARTITIONED ON (...
1
vote
0answers
24 views

Azure SQL - Modifying Indexed Views without downtime

We have a SQL Azure database with a large indexed view that supports a website with high traffic during business hours. One of our goals from a DevOps perspective to be able to ship software (...
1
vote
0answers
35 views

Cronjob to check when Oracle Materialized View was last updated?

I have two database users in my Oracle DB and want to create MV's in User B which get their data from User A through a database link. I plan to autorefresh the MV's every night but want to make sure ...
1
vote
0answers
244 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: ...
1
vote
0answers
33 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,...
1
vote
0answers
616 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 (...
1
vote
0answers
54 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 +-----...
1
vote
0answers
118 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 ...
1
vote
0answers
314 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
0answers
407 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 ...
1
vote
0answers
930 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 ...
1
vote
0answers
488 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a Azure V12 SQL Server query using indexed views. I have one slow element, which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped ...
1
vote
0answers
120 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
1
vote
0answers
860 views

Update column type when materialized views depend on the column?

I'm working in Postgres 9.4. I want to alter the type of a column in a table: ALTER TABLE mytable ALTER COLUMN quantity TYPE integer; But I get this: ERROR: cannot alter type of a column used by a ...
1
vote
0answers
205 views

MQT automatic `set integrity off` when underlying table updated

Have a slow query. Need an MQT so the optimizer will rewrite the query and use my MQT instead. Problem: a manual full refresh is much much faster than using refresh immediate type or a staging table: ...
1
vote
0answers
108 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 w....
1
vote
0answers
219 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 ...
0
votes
1answer
79 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 ...
0
votes
2answers
216 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 ...
0
votes
1answer
1k 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 ...
0
votes
2answers
1k views

Materialized views Performance

Postgres’ docs note: While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; ...
0
votes
1answer
64 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
1answer
101 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 ...