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

Materialized view slow with Timescale

I have a table created like this: -- enable timescale plugin CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; -- create schema CREATE SCHEMA IF NOT EXISTS {schemaName}; -- create table CREATE ...
  • 235
0 votes
0 answers
24 views

Refresh materialized views upon database restore

Our database (PG 12) uses a few materialized views, some depending on others. We use pg_dump for backups and psql to restore. And, we do this restore every once in a while, for example when upgrading ...
  • 381
0 votes
0 answers
27 views

Is calling a stable function performance comparable to reading values from a table?

Let's say we have a function _test_has_group_feature, which is meant to check if a group (identified by a name) has a particular feature (identified by name). _test_has_group_feature(group_name, ...
  • 1,026
0 votes
1 answer
30 views

How can I sort thousands of Replies on a Post, following an algorithm that's not just based on columns of the "Replies" table?

On my website, Replies to Post are sorted based on an algorithm that depends on multiple factors: Reactions, Reply Quality, User Reputation, Reports, Replies to Reply, etc... (these are just a few ...
  • 748
0 votes
0 answers
31 views

Is there a formalism for the kinds of constraints we can implement with indexed-views? Or at least a rule-of-thumb?

SQL Server’s Indexed Views provide a performant way to implement certain classes of database-scoped constraints - such as ensuring a value is unique over multiple tables - or other non-key, cross-...
  • 480
0 votes
1 answer
48 views

Help Troubleshooting Full-text Search Function Performance

Query finds posts with a 10 mile-radius of postal code. Response time is much too high Number of rows in the PG Materialized Table: 520,000 Any help would be appreciated. Query: EXPLAIN ANALYZE SELECT ...
  • 1
0 votes
0 answers
144 views

Generating Triggers from the Materialized View Query

We are currently using materialized views to reduce the response time of our website. Some views take a few seconds to refresh. This results in long loading times for the user. Generally speaking, it ...
  • 1
1 vote
1 answer
83 views

Do missing index DMVs work with indexed views?

I've been delving into query store to find the cause of some high CPU usage and found some potential index improvements to some existing indexed views by looking through the plans of the highest CPU ...
0 votes
0 answers
28 views

Querying across Many to Many Relationship to find all unique elements in jsonb array

Imagine two tables one for Collections that has many posts and one for Posts that has many collections it can in. The Post table has a Tags column that stores multiple tags in a array stored as a ...
1 vote
1 answer
44 views

Improving postgreSQL 13.3 performance on a materialised view?

I found one of our APIs using this query in our materialised view which generates aggregated report on asset inventory (PostgreSQL 13.3) and is very slow on a table with 1.2 million rows SELECT ...
  • 111
0 votes
1 answer
289 views

Postgres Materialized Views keeps getting dropped every time underlying views is updated

I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view. The MVs are in Schema B and Views are in Schema A. They are created like create SchemaB.mv1 as ...
0 votes
0 answers
40 views

FAST refreshable MVIEW moving to COMPLETE refresh only

I have created below FAST refresh MVIEW which does couple of FAST refreshes immediately after creation and all subsequent refreshes are happening as COMPLETE refresh ( DELETE + full INSERT) CREATE ...
0 votes
2 answers
319 views

Why does Postgres not use an index when joining a materialized view?

I have a table defined as: CREATE TABLE addresses ( "address" varchar(42), "other_info1" text, "other_info2" bigint ); and I have some manual corrections to ...
0 votes
0 answers
95 views

Populate materialized view based on inherited ltree relations

I am trying to create a materialized view that populates the inherited relations of a ltree. Assuming we have 3 tables: hierarchy --- id: int name: text path: ltree user --- id: int name: text ...
  • 101
1 vote
2 answers
62 views

Problem with Oracle Job Scheduling

I have a job that refreshes a materialised view refresh group on Oracle 19c. I wanted to refresh the group daily at midnight - trunc(sysdate)? The job was made using using the following PL/SQL: ...
  • 137
0 votes
0 answers
353 views

Refresh materialized view on Postgresql 11 on RDS

We are currently on Postgres 11.13 on AWS RDS. I am trying to create a materialized view that takes about 6-7 minutes to run. What is the best way to keep this MV mostly up to date? I was thinking of ...
  • 101
0 votes
1 answer
162 views

postgresql : conflict with simultaneous "create materialized view"

This is a continuation on my old question Multiple concurrent "REFRESH MATERIALIZED VIEW": how to manage? Now I'm unfortunately struggling with the real thing after having been more ...
  • 115
1 vote
1 answer
941 views

Naming conventions for views and materialized views: "*_v", "v_*", "*_mv", "mv_*" or none? [closed]

My view stores quality metrics for various gizmos1 in the database. Today I designed it to be a view. In a few years, as we produce more gizmos, it may need to be changed to a materialized view. I was ...
0 votes
0 answers
536 views

Postgres materialized view: no performance gain

I've created a materialized view to cache results of joining about 15 tables. Unfortunately, select * from mv_cache takes the same amount of time as the original joins. Am I doing anything wrong? ...
0 votes
1 answer
260 views

PostgreSQL : Refresh a materialized view base on a foreign table

I've got a foreign table from a distant csv host on a external webserver. I need to enhance this table so I create a materialized view to add some extra fields and join with internal data. Making a ...
0 votes
0 answers
172 views

Materialized view creates quickly with data but cannot refresh

I am working with a database on Amazon RDS and have a specific query which joins about 6 tables on keys. Creating the materialised view with data takes about 3 minutes and behaves as expected with ...
1 vote
1 answer
176 views

List Postgres materialized views the current user has select grant

With select matviewname from pg_matviews I get all materialized views in the database. How can I filter the result to get only those the current user has select grant (= read permission) on?
  • 113
4 votes
1 answer
157 views

Indexed view maintenance with foreign key not elided when inserting parent row

TL;DR; Given an indexed view that has a JOIN between two tables that already have a foreign key relationship along with a WHERE predicate on the parent table. When inserting into the parent table of ...
  • 8,383
1 vote
1 answer
136 views

Oracle multithreaded JDBC bulk select high-performance data export

The goal of our solution is to export as fast as possible CLOB data from an events table. The concept is to logically partition the data with a partition_id column such that every thread from the ...
  • 11
2 votes
1 answer
341 views

postgresql materialized view ERROR: a negative number raised to a non-integer power yields a complex result

I have complex view with calculations but one line makes issue ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric) When I execute the query, ...
  • 123
0 votes
0 answers
674 views

Oracle mview refresh taking long time

the customer I work for is used to launch a script to refresh mviews on its db. Refresh command for all mviews is issued in the following way: DBMS_MVIEW.refresh('"OWNER"."MVIEW"','...
0 votes
0 answers
570 views

How can I exclude Materialised Views from a pg_dump

I'm doing a pg_dump of an AWS RDS database so I can use it for local development, but there are some large tables I want to exclude. I've successfully removed them using the --exclude-table=blah* flag ...
1 vote
3 answers
2k views

Quering millions of rows is still slow with index and a materialized view

The system has 4 tables that are joined to get a lot of data about users, this query was turned into a view with 37 columns and a total of ~8 million rows. Eventually this became slow due to a user ...
  • 1,109
0 votes
1 answer
564 views

Postgres metadata: get a list of all MATERIALIZED VIEW columns, with data type, INCLUDING array columns

I need to get metadata about all MATERIALIZED VIEW columns, including their data type. I can do this with this query: SELECT trim(leading '_' from pg_type.typname) as data_type, pg_namespace....
  • 253
0 votes
1 answer
23 views

List sorted Posts on Group and children Groups efficiently

Say I have a system that has Groups and Posts on those Groups. A Group can have multiple "child Groups" Group 1 > Group 2 > Group 3 Group 1 > Group 2 > Group 4 Group 1 > ...
  • 748
4 votes
1 answer
1k views

Is vacuuming a PostgreSQL materialized view necessary?

I understand it makes sense to analyze a Materialized View (MV), but since MVs are not updateable, does that imply that we need not ever vacuum a MV?
0 votes
0 answers
238 views

Convert jsonb column into view for Grafana

I have a partially dynamic data that I have to save into Postgres 13 and then visualize it in Grafana. Example for one document insert (partial) { "Timestamp": { "$date": { ...
  • 11
0 votes
1 answer
672 views

Need help understanding Postgres materialized views under the hood

postgres 9.6.20 Need some help getting an accurate mental model of whats happening with postgres materialized views. Based on this from postgres docs (https://www.postgresql.org/docs/9.6/rules-...
  • 103
2 votes
3 answers
58 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
2 answers
273 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,437
-1 votes
2 answers
895 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 ...
  • 151
0 votes
0 answers
1k 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 ...
  • 101
0 votes
1 answer
2k 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 | ...
5 votes
1 answer
596 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 ...
  • 8,383
0 votes
2 answers
89 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 ...
  • 1,437
0 votes
1 answer
56 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; --...
  • 1,437
7 votes
2 answers
539 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 [...
  • 173
1 vote
2 answers
562 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 ...
  • 1,437
1 vote
2 answers
348 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 ...
  • 1,437
0 votes
1 answer
906 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,437
1 vote
1 answer
178 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....
  • 1,437
0 votes
1 answer
260 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 ...
  • 1,437
0 votes
1 answer
654 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 ...
  • 1,437
8 votes
2 answers
397 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
1 answer
361 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 ...
  • 29.7k

1
2 3 4 5 6