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
0answers
24 views

TSQL Materiaized/Indexed View Cluster index with subquery

I have two tables: Listings and Media and Media is a child of Listings. I am trying to create a indexed view that will include the first record from Media. I would also like to index my view so that ...
0
votes
1answer
13 views

What permissions are required for complex cross-schema materialized view in Oracle?

I have a complex cross-schema query that I'd like to create as a materialized view in Oracle 19c (although the database has been ported forward from numerous historical versions of Oracle). This ...
0
votes
1answer
9 views

Kill concurrent materialized view refresh PID

Concurrent refreshes of materialized views don't have a record in pg_stat_activity, other than the initial (~immediately completing) query to start them. Is there some other way to obtain the PID, in ...
0
votes
0answers
11 views

Redshift - Why do materialized view disappear? (using Matillion)

I can create materialized views in redshift like explained in the documentation: CREATE MATERIALIZED VIEW tickets_mv AS select catgroup, sum(qtysold) as sold from category c, event e, sales s ...
0
votes
1answer
24 views

Converting rows to columns using crosstab in PostgreSQL not working (relation “table” does not exist)

I need to use the compiled data using CTE and then convert the columns to rows using crosstab(open to other ideas) in the next select statement. Below is the query. with checked_adgroup AS ( ...
1
vote
1answer
24 views

Caching derived dataset automatically in SQL Server

I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there ...
0
votes
2answers
29 views

Postgresql MVIEW refresh from Oracle Materialized View Log

Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is ...
1
vote
1answer
19 views

Insert into indexed view?

I give an example to show my problem. I create 2 table as the following: CREATE TABLE a ( id INT ) CREATE TABLE b ( name NVARCHAR(10), id INT ) Then insert data into these tables ...
0
votes
1answer
57 views

Trigger on indexed view

I created an indexed view with a INSTEAD OF trigger on it, but it's not firing when inserts happen at the base table level. This is the plan that a normal insert generates (note the clustered index ...
1
vote
1answer
68 views

What is the rationale of dropping all indexes for an altered view in SQL Server?

This is a follow up of ALTER VIEW drops Index from View question. The accepted response indicates that ALTER VIEW will automatically drop any defined indexes. This is somewhat counter intuitive to ...
11
votes
2answers
553 views

ALTER VIEW drops Index from View

as the header line suggests: Using an ALTER VIEW statement on a VIEW that has an index on it will drop this (all?) indexes from the VIEW without warning. I would prefer the ALTER VIEW statement to ...
0
votes
0answers
18 views

Constructing materialized view query

I'm working on an existing app which has the structure (simplified and) described below. One of our new queries is to find all activity in a company which is rather complicated and non-performant to ...
2
votes
0answers
25 views

MSSQL: Conditional noexpand hint for view that may be indexed or not

As documented in other Q&A on this site, if you create an indexed view you may have to give the noexpand hint to use it as such. Without the hint, cheaper versions of MSSQL will always treat it ...
0
votes
1answer
29 views

What are the approaches for refreshing materialized views in Oracle, when underlying tables are updated often?

I am a web developer, maintaining a web app that tracks orders, customers, products, etc, that my client uses internally. I use Oracle 12c, hosted on AWS RDS. My client has just switched some other ...
0
votes
1answer
29 views

Query FROM Indexed View queries underlying table in Execution plan

I have a largish table (>126 million rows), and a need to optimize a specialized set of queries, so I created an Indexed view on the table, and coded a procedure that would select from the Indexed ...
0
votes
1answer
48 views

Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is ...
0
votes
0answers
33 views

Creating a Real Time Materialized View and Materialized View Logs

I wanted to create a real-time materialized view with a fast daily refresh based on a query that took fields from 3 tables (PS_JOB, PS_DEPT_TBL, PSOPRDEFN) and 1 view(PS_NPS_LICCERT_VW). The query/...
0
votes
0answers
57 views

Getting View of One to Many where Column Names are repeated with Indexed field

I have 4 tables joined with an inner join. 2 of these joined tables are 1 to Many relationships. Consider the following example return where t1 is table 1, t2 is table 2 and so forth. table 1 has a 1 ...
1
vote
1answer
39 views

Rebuild materialized view from massive base tables

We had a materialized view over two bae tables, which was set to refresh on demand, and we had an hourly job which fast refreshed the mview after a linux script inserted new records to the base tables....
0
votes
0answers
71 views

Create event/lambda from materialized view refresh in PostgreSQL

I have a materialized view in PostgreSQL that I refresh say every fifteen minutes. I want to create an event triggered if that materialized view is not empty upon refresh (so that I can send AWS ...
0
votes
1answer
103 views

Created a Materialized View in ORACLE which won't refresh

I created a materialized view log along with a materialized view called update_nboe based on table NBOE_ EMPLOYEES_TEST using the following code CREATE MATERIALIZED VIEW LOG ON NBOE_EMPLOYEES_TEST ...
1
vote
1answer
142 views

Postgres: How can I force index usage for view with latest rates?

We import currency rates into DB: CREATE TABLE currency_rate ( id int8 NOT NULL, date date NOT NULL, currency varchar(3) NOT NULL, rate numeric(12,6) NOT NULL, CONSTRAINT ...
7
votes
1answer
331 views

SQL Server - Is there a way to prevent shared range locks to 'infinity' when using an indexed view?

I have your basic header/details table (think orders and order details). The header table has an identity column as the clustered key, the detail has the header id and a line number column as the ...
1
vote
1answer
269 views

Multiple concurrent “REFRESH MATERIALIZED VIEW”: how to manage?

I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a ...
0
votes
1answer
19 views

group several columns together in materialized view

For statistical reasons, I need to group several ids in a single column of a materialized view. This should form a list of ids from other tables with an info. Further processing will be done on this ...
0
votes
1answer
37 views

How to create a clustered index on a schema bound view with the least amount of contention caused?

I have a table with 100 billion rows in it, with an identity BIGINT column that is the primary key (and clustered index). I have a schema bound view on that table that is filtered down to the last 500 ...
0
votes
0answers
10 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
2
votes
1answer
127 views

Why can't the query processor produce a query plan with a FORCESEEK hint when I'm INNER JOINing by the clustered index on both indexed views?

I'm getting the following error when I try to use the FORCESEEK hint with the query below: Msg 8622, Level 16, State 1, Line 96 Query processor could not produce a query plan because of the hints ...
0
votes
1answer
29 views

Why when I JOIN to a casted field in an index view, the execution plan warns about “type conversion in expression”?

I thought an indexed view was materialized and therefor stored on disk. If that's the case, I would've thought that any type conversions I'm doing in the view would materialize as that datatype on ...
1
vote
1answer
48 views

Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the ...
12
votes
1answer
2k views

Why is casting a DATE field to VARCHAR datatype non-deterministic and is there a way to make it deterministic?

I'm trying to create an indexed view where the index is on a field in the view which is a hash of a bunch of the columns of the underlying table in the view. Here's an example: CREATE VIEW ...
1
vote
1answer
118 views

How to manage disk space allocation for materialized views?

Summary: I have materialized views in oracle 11g that seem to hog disk space, unlike normal tables that mark rows as deleted and stats eventually show them as free space (allocated to the table, ...
0
votes
0answers
23 views

Is subscription database considered as read-only?

I am not sure about that because while users implement SELECT from subscription database, publisher replicates data to the subscription server meaning that INSERT and UPDATE also take place (which are ...
2
votes
1answer
193 views

Indexed view referencing objects on two different schemas

When I try to create/alter a view to create an index like this CREATE UNIQUE CLUSTERED INDEX IDX_vSalPopulation ON sfdc.vSalPopulation (ID); I get the following error message Msg 1938, Level ...
1
vote
0answers
39 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 ...
2
votes
2answers
108 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 ...
0
votes
0answers
50 views

Materialized view bigger than table it is based on?

I have a database that's ~130 GB and one table makes up for most of this (85 GB). Because this table holds a lot of duplicates, we have a materialized view which basically uses a window function to ...
4
votes
1answer
129 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...
1
vote
1answer
45 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 ...
6
votes
1answer
519 views

Why SQL does not use the indexed view?

I have the following version of SQL Server: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) I created ...
1
vote
0answers
264 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
32 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
1answer
47 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
0answers
48 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 ...
0
votes
0answers
54 views

Create indexed view with fluctuating data behind

I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are ...
0
votes
0answers
44 views

mysql run out of memory during mview refresh

I want to create mview from mysql to Oracle using DBLink via odbc driver with Linux 64 bit os. but due to large number of Records around 2.5 cr records it raise the error ERROR at line 1: ORA-28500: ...
0
votes
2answers
2k 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
186 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
0
votes
1answer
150 views

Getting ORA-32042 even though i can create the view in SQL Developer

I have a strange problem creating a materialized view using the code generated by the export wizard. I have created a materialized view and pasted following code into the sql query tab from the popup....
2
votes
2answers
1k 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
2 3 4 5