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.
282
questions
1
vote
0
answers
48
views
Continious aggregates (keeping aggregated data in a fresh state) for PostgreSQL
I build a PostgreSQL database for movie db platform. This DB is heavy loaded for readings of many related data.
Here is simplified schema what I have:
create table items
(
id ...
- 143
0
votes
0
answers
13
views
Detect Delete/Insert/Update in MatView Refresh Concurently
I have a matview which I refresh once in a hour.(50M rows). By running
refresh materialized view concurently my_view;
Note - MatView contains a unique Index too, on column unique_id.
I have a table ...
- 101
0
votes
0
answers
16
views
Materialized view creation times out after too many creation/deletion with the same name
I was trying to create a MV on a Postgres db, on a table with a few millions records.
During the development process, I migrated & rolled back (CREATE MATERIALIZED VIEW / DROP MATERIALIZED VIEW) a ...
- 101
0
votes
0
answers
28
views
Postgres 13: Update column when MV refresh is complete
I have what feels like a very simple operation I wish to perform, but cannot find any guidance.
I have an application that allows users to upload a series of files with records, which get processed ...
- 1
0
votes
0
answers
21
views
Storing Aggregation pipeline inside materialized view in Mongo
I am quite new to Mongo Coming from Sql background.
I have a aggregation on collection_1 which involves $graphlookup (given below)
[
{
$match: {
$or: [
{
objStatus: "...
- 13
0
votes
1
answer
127
views
Slow count on empty table unexplained
My incremental materialized view refresh starting to become slower and slower. Each day there are millions of new records. So there are many deletes on the mv log table. After investigation I see one ...
- 513
0
votes
0
answers
157
views
Refreshing materialized view blocks an unrelated table
Last few days I was experiencing a problem that seems really strange to me and even though I have some experience with PostgreSQL, I really don't understand what may be the cause.
We have two services ...
0
votes
1
answer
94
views
ORA-12015 materialized view complex query error with simple join two table
I have 2 simple table which has columns like :
table1 :
"OBJECTID" NUMBER NOT NULL ENABLE
"OLCEK_ID" NUMBER(*,0)
table2 :
"OBJECTID" NUMBER NOT NULL ENABLE
i created ...
0
votes
0
answers
243
views
ORA-12034 while doing a COMPLETE refresh
Trying to understand how it's possible to get ORA-12034 while doing a COMPLETE refresh?
Oracle 19.18.0.0.0
All the documentation/notes/metalink indicates this is an error for FAST refresh mat views, ...
- 506
4
votes
4
answers
439
views
Is there a way to disable logging / backups for view indexes?
We have a SQL Server database that uses indexed views to allow for efficient querying of some data that changes regularly (yes I know there's a cost to maintaining the index when the data changes but ...
- 143
1
vote
1
answer
276
views
Should we reconsider using materialized views in Cassandra?
There is a bit of talk lately around the use of materialized views (MV) in Cassandra. As an ordinary user, it is difficult to know whether it is or it isn't a good idea to use MVs.
What is the ...
- 3,280
3
votes
0
answers
37
views
How does a where clause in a view affect how the db engine processed DML with respect to indexed views? [duplicate]
Imagine a database where you have an indexed view aggregating data from 2 tables, a dataset table and a datapoints table (master/detail).
When a dataset is first created it has a status of LOAD. The ...
- 141
1
vote
1
answer
165
views
Time based incremental load query optimization and index design
Problem
I try to optimize an extract/load query on a highly normalized table structure. Extract/load is implementing incremental loading using a datetime column to determine only records which have ...
- 11
2
votes
1
answer
265
views
Error while creating continuous aggregate (materialized view) when source and new time columns have the same name (PSQL/TSDB)
I'm facing a problem when creating a continuous aggregate on timescaledb (based on materialized views) when the source time column and the new time column have the same name like below:
CREATE ...
- 287
3
votes
1
answer
1k
views
ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function
I need to execute CREATE MATERIALIZED VIEW ... WITH DATA from a function/procedure but I'm getting ERROR: CREATE MATERIALIZED VIEW ... WITH DATA cannot be executed from a function.
There is any ...
- 287
0
votes
2
answers
310
views
Unable to Create Fast Refresh Materialized View with Geometry Field
I am receiving the following error while trying to create a materialized view with a geometry field on Oracle (21c):
ORA-12015: cannot create a fast refresh materialized view from a complex query
I ...
- 3
1
vote
0
answers
71
views
PostgreSQL trigger for statement explanation
PostgreSQL 14
I try to understand the behaviour of trigger for each statement.
Here is an example :
AFTER INSERT OR DELETE OR UPDATE
ON mytbl
FOR EACH STATEMENT
EXECUTE FUNCTION ...
- 207
9
votes
1
answer
2k
views
How does SQL Server update indexed views?
Every time you insert, update, or delete data in the base table(s) that the indexed view references, the database engine will automatically update the indexed view to reflect the changes. However, I'm ...
- 1,159
0
votes
0
answers
165
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 ...
- 283
0
votes
0
answers
350
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 ...
- 371
0
votes
0
answers
30
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,145
0
votes
1
answer
38
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 ...
- 809
0
votes
0
answers
45
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-...
- 564
0
votes
1
answer
59
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
1
vote
1
answer
113
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 ...
- 172
1
vote
1
answer
58
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
856
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 ...
1
vote
2
answers
754
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 ...
- 21
1
vote
2
answers
114
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
1
vote
0
answers
542
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 ...
- 111
0
votes
1
answer
325
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
2
votes
1
answer
3k
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 ...
- 153
0
votes
0
answers
959
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
496
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 ...
- 1
0
votes
0
answers
202
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 ...
- 46
1
vote
1
answer
607
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
212
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 ...
- 11.8k
1
vote
1
answer
214
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
422
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
1k
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"','...
- 9
0
votes
0
answers
1k
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 ...
- 101
1
vote
3
answers
5k
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,183
0
votes
1
answer
1k
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....
- 303
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 > ...
- 809
6
votes
1
answer
2k
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?
- 96
0
votes
0
answers
383
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
1
vote
1
answer
954
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-...
- 113
2
votes
3
answers
80
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 ...
- 21
1
vote
2
answers
369
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,447
-1
votes
2
answers
2k
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