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
2
votes
3answers
52 views

Preserving full refresh data source

I have a SQL Server database that gets updated every week with a completely new set of data. I am currently using two staging databases. A series of view databases are connecting to these and one week ...
1
vote
2answers
117 views

Oracle MV requires object type to be defined as FINAL?

I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY: The MV would do a COMPLETE refresh on a schedule — on a local table. Some info ...
-1
votes
2answers
67 views

What is the correct way to do a computed column with complex logic with performant sorting and filtering at scale in MS SQL Server? [closed]

I'll provide a toy example here: I have 2 tables, a Thing table and an AdditionGroup table. Things may be members of an AdditionGroup, or they may not. Both Thing and AdditionGroup have a Quantity ...
0
votes
0answers
34 views

Can't refresh Materialized View Concurrently

I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error: SQL Error [XX000]: ERROR: unexpected attlen: -2 Where: SQL statement "SELECT ...
0
votes
1answer
26 views

Postgres SQL: View with a nested table

I have 2 tables users & groups: user_id | title group_id | title ---------------- ------------------ U_1 | User 1 G_1 | Group 1 U_2 | User 2 G_2 | ...
6
votes
1answer
164 views

Updating an indexed view with NOEXPAND

I will preface this by saying that this is a cross post from an unanswered question on Stack Overflow. I am not doing this simply to get more views on the question, I would like the DBA community to ...
0
votes
2answers
82 views

Report based on MV: Deliberately make the report fail if MV is out of date?

I have a query that is used in a BIRT report (in IBM's Maximo Asset Management platform). I'm considering storing the query in a materialized view in order to improve performance and reduce the load ...
0
votes
1answer
20 views

MV on remote/master table fails to resume syncing (after master db host was temporarily unreachable)

I have a fast-refresh materialized view in a target db (18c) that syncs from a remote table/master db (19c): --master db (19c) create materialized view log on master.workorder with primary key; --...
5
votes
1answer
188 views

Indexed view not used in actual execution plan?

I've got a modest use case for an indexed view, which collapses out a column and sums all entries from a large table: CREATE VIEW dbo.Losses_CombinedPortfolio WITH SCHEMABINDING AS SELECT [...
0
votes
2answers
150 views

Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?

I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column. Options for the GEOMETRY column datatype include: ESRI's proprietary ...
0
votes
2answers
45 views

Materialized View: What is the Update Trigger property?

I've created a materialized view in an Oracle 18c database. When I look at the Info tab of the materialized view in Toad, I see that there is a property called Update Trigger. What does the Update ...
0
votes
1answer
77 views

Create fast-refresh MV over dblink on table without PK?

I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink. The MV would refresh on a 30-second schedule (since on-commit is not ...
1
vote
1answer
50 views

Why am I able to create MV logs using PK — even though there is no PK?

I have a table that does not have a primary key: I'm able to create MV logs on the table — without specifying whether to use the primary key or the rowid: create materialized view log on maximo....
0
votes
1answer
42 views

Materialized view logs — with condition?

I've created materialized view logs on a WORKORDER table in an Oracle 19c database. create materialized view log on my_workorder_system.workorder with rowid; The plan is to integrate the WORKORDER ...
0
votes
1answer
131 views

Materialized view not purging (over dblink via view)

I've created a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c): create materialized view primary_system.workorder_mv build immediate refresh force on ...
8
votes
2answers
288 views

Inability to query underlying table of a schema-bound view

I migrated a database from SQL Server 2008R2 to SQL Server 2019 (both Enterprise Edition) using the backup-restore technique, and a set of post-transfer actions like DBCC UPDATEUSAGE or UPDATE ...
1
vote
1answer
79 views

Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?

The Backstory: I have a table with the following structure: CREATE TABLE WideTable1 ( BoringColumn1, BoringColumn2, CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see ...
1
vote
0answers
117 views

Force select * in postgres materialized view definition

I have a Postgres database and a family of materialised views defined as clones of tables, used for testing and analysis of data so the data won't change during the testing/analysis process like: ...
0
votes
1answer
272 views

What does Oracle impdp do when a name clashes with a materialized view or synonym?

I have a production Oracle database, and I want to export tables, data, views, materialized views, synonyms, etc, to import into a development Oracle database. Actually, this is part of my job, and we ...
3
votes
1answer
275 views

Postgresql: Refreshing materialized view fails with “No space left on device” and a huge spike in disk usage

I have a materialized view that refreshes every five minutes. The SQL aggregates the data among many tables with over 800k rows in each. However, the when using the "REFRESH MATERIALIZED VIEW ...
0
votes
1answer
135 views

How do I force a Writable Materialized View instead of an Updatable one?

How do I ensure that an Oracle Materialized View is created as a "Writeable" materialized view and not as an "Updatable" one? I never want these views to push data back to the ...
0
votes
2answers
418 views

Materialized view in SQL Server

Have a bit of glitches from the time I was using SQL Server back in 2006 and the many other databases I played with (and which I have not touched since then). I need to create a materialized view to ...
0
votes
0answers
13 views

PostgreSQL - Refresh dblink materialized views with incremental data [duplicate]

I have a few PostgreSQL servers(v12). I want to bring the data from these servers to my central server in a frequent interval.(No options for logical replication). It may be crazy, but I'm thinking of ...
0
votes
0answers
250 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
287 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
93 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
1answer
270 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
28 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
141 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
40 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 ...
1
vote
1answer
178 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
75 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 ...
12
votes
2answers
664 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 ...
2
votes
0answers
27 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
92 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
34 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
534 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
59 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
52 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
1answer
259 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
1k 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 ...
9
votes
1answer
462 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
1k 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
23 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
40 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
12 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
726 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
31 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
49 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
2 3 4 5