Stack Overflow
Stack Overflow

Questions tagged [materialized-views]

A materialized view is a database object that contains the results of a query. They can be local copies of data located remotely, or can be used to create summary tables based on aggregations of a table's data. Materialized views are also known as snapshots.

A materialized view is a database object that contains the results of a query. They can be local copies of data located remotely, or can be used to create summary tables based on aggregations of a table's data. Materialized views are also known as snapshots.

When posting questions with the materialized-views tag please add another tag with the RDBMS involved(Oracle, Sql Server, etc.)

Oracle's Materialized Views documentation here

839 questions
4
votes
1 answer

ORA-01722: invalid number when creating materialized view

I am creating a view and the same view converted into materialized view in the same system. But doing the same thing in another system I got error ORA-01722: invalid number when creating the materialized view. Why? create materialized view…
user1357722
  • 7,088
  • 13
  • 34
  • 43
3
votes
2 answers

Create materialized view much slower than create table

I've got a query that I'd like to use to back a materialized view. When I use this syntax to create a regular table from the view, it executes very quickly (5 seconds): create table my_table as select * from my_view; However, when I try to create…
user1212274
  • 111
  • 7
3
votes
1 answer

Refreshable on commit materialized view using MAX()

I'm being hit hard by the entity-attribute-value antipattern. Some day, years ago, a guy decided that DDL wasn't sexy, and wanted to develop something "flexible enough" to keep information about people. He ignored the fact that people uses to have…
Samuel
  • 2,430
  • 2
  • 19
  • 21
3
votes
3 answers

Why does a ORA-12054 error occur when creating this simple materialized view example?

ALTER TABLE RECORDINGS ADD PRIMARY KEY (ID); CREATE MATERIALIZED VIEW LOG ON RECORDINGS TABLESPACE USERS NOLOGGING; DROP MATERIALIZED VIEW REC_SEARCH_TEST; CREATE MATERIALIZED VIEW REC_SEARCH_TEST REFRESH COMPLETE ON COMMIT AS ( SELECT…
Moz
  • 1,494
  • 6
  • 21
  • 32
3
votes
1 answer

Nested materialized views find out actions on commit?

I'd like to find out what actions (i.e. selects, inserts, updates) a database does in response to say: insert into T (col_x, col_y, col_z) values (val_x, val_y, val_z); T in this case is a table with a materialized view log but has a tree of nested…
Clinton
  • 22,361
  • 15
  • 67
  • 163
3
votes
0 answers

Docker imagebuild for Postgresql initializing materialized view

I face weird problem I'd like to understand with respect to postgres initialization at docker image build. My base image is FROM bitnami/postgresql:14.5.0 Where I mount a custom SQL init script into, executed at /docker-entrypoint-initdb.d…
Arno
  • 308
  • 3
  • 10
3
votes
1 answer

Gather statistics on materialized views as part of gather_schema_stats

We use the following to refresh statistics for all tables in a given schema: exec dbms_stats.gather_schema_stats(ownname => 'some_schema', estimate_percent => dbms_stats.auto_sample_size, cascade => true, method_opt => 'FOR ALL COLUMNS SIZE AUTO',…
grenade
  • 31,451
  • 23
  • 97
  • 126
3
votes
1 answer

Tablespaces used to create a materialized view?

I'm trying to create a view as such: CREATE MATERIALIZED ReasonableSizedView TABLESPACE MyMediumTS AS select COUNT(something) AS allsomethings, thetype AS thing, status from SomeMassiveTable where thetype = 'x' AND status IN…
filippo
  • 5,583
  • 13
  • 50
  • 72
3
votes
1 answer

Postgresql View aggregating data

I've got a problem to make this "nice" and efficient, as well as easy to read. Unfortunately it's lacking both properties. Given a table with date,transaction_type,username and credits, I want to generate a view which summarizes into these fields:…
loki.dev
  • 172
  • 6
3
votes
0 answers

Postgresql Incremental Materialized View Maintenance (IVM) challenges, why delay it again to targetversion 16?

The latest message from the commit fest https://wiki.postgresql.org/wiki/Incremental_View_Maintenance#CommitFest is saying "Changed targetversion to 16" so it seems this feature will not arrive in 2022. The initial patch was submitted in 2019 and…
acristu
  • 721
  • 6
  • 19
3
votes
1 answer

Oracle - Materialized View, can this MV be fast refreshable?

I have a materialized view that looks somewhat like the following and I'm wondering if there is anyway to have this materialized view 'fast' refreshable? Basically, I'm asking the following: Can a materialized view contain oracle functions such as…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
3
votes
0 answers

Bigquery Materialized view on partitioned table

I'm not able to create materialized view over a partitioned table, even though I have added partition filter in the query itself. Source Table has partition column ts:timestamp, partitioned by date(ts) Query for materialized view: create…
Aniket Awati
  • 1,381
  • 3
  • 13
  • 21
3
votes
1 answer

Get Inner Table Name of Materialized Views with Clickhouse

I have several Tables and materialized views which haven't been created with To [db] statement and have an inner tables with these names: │ .inner_id.27e007b7-d831-4bea-8610-b3f0fd1ee018 │ │ .inner_id.3e27a1eb-f40f-4c6d-bc70-7722f2cc953b │ │…
Mohammadamin Aryan
  • 77
  • 1
  • 8
3
votes
3 answers

materialized views on external database fail to refresh at specified time - SQL*Net more data from dblink

We use a web-based service that runs on Oracle. They are strict about only allowing SELECT-only ODBC access. Some of the reporting that we do isn't well accounted for by the views that the company provisions, so we set up a db_link using the…
Andrew
  • 9,090
  • 8
  • 46
  • 59
3
votes
1 answer

Materialized View for Latest Rows by ID in a BigQuery Table?

I have a BigQuery table with ~5k unique IDs. Every day new rows are inserted for IDs that may or may not already exist. We use this query to find the most recent rows: SELECT t.* EXCEPT (seqnum), FROM (SELECT t.*, ROW_NUMBER() OVER…
ProGirlXOXO
  • 2,170
  • 6
  • 25
  • 47
1 2 3
…
55 56