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
11
votes
4 answers

Is it possible to partially refresh a materialized view in Oracle?

I have a very complex Oracle view based on other materialized views, regular views as well as some tables (I can't "fast refresh" it). Most of the time, existing records in this view are based on a date and are "stable", with new record sets having…
Galghamon
  • 2,012
  • 18
  • 27
10
votes
2 answers

Materialized view and table with the same name

I kind of understand materialized views and have worked with them before. Recently a question came up as to why a particular report didn't show latest data, I looked into the issue. Apparently, they had a temp table loaded with crontab earlier and…
svaratech
  • 241
  • 1
  • 4
  • 17
10
votes
2 answers

How to introspect materialized views

I have a utility that introspects columns of tables using: select column_name, data_type from information_schema.columns where table_name=%s How can I extend this to introspect columns of materialized views?
shaunc
  • 5,317
  • 4
  • 43
  • 58
9
votes
5 answers

Table vs Materialized View

This is related to the previous question I asked, Saved View with a timestamp expression, about storing information in a (non-materialized) View. How would the data be stored and then retrieved when a user does: CREATED MATERIALIZED VIEW mv AS…
David542
  • 104,438
  • 178
  • 489
  • 842
9
votes
3 answers

the best way to track data changes in oracle

as the title i am talking about, what's the best way to track data changes in oracle? i just want to know which row being updated/deleted/inserted? at first i think about the trigger, but i need to write more triggers on each table and then record…
GBK
  • 375
  • 2
  • 4
  • 11
9
votes
1 answer

How can I pg_dump a materialized view as a materialized view and not a table?

I'm attempting to pg_dump (v9.3.4) from one db and pg_restore into another (v9.3.6). I have several materialized views (some with indexes and some with no indexes). One of my materialized views is being dumped as a table (I have confirmed this by…
three-cups
  • 4,375
  • 3
  • 31
  • 41
9
votes
2 answers

Materialized View vs View

May I know the difference for these two items? Data in materialized view can be refresh but so as view when we use select statement. Why not just use view instead of materialized view?
4 Leave Cover
  • 1,248
  • 12
  • 40
  • 83
8
votes
1 answer

Remove tablespace info from Materialized View DDL

Using the following SQL, the DDL for a given materialized view can be obtained. BEGIN DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE); DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,…
grenade
  • 31,451
  • 23
  • 97
  • 126
8
votes
1 answer

Check a materialized view exists?

How do I check if a materialized view exists? I have created one and checked in information_schema.tables and information_schema.viewsbut I cannot see it. Where should I be looking?
Guerrilla
  • 13,375
  • 31
  • 109
  • 210
8
votes
2 answers

How to create typeorm entity for postgresql MATERIALIZED VIEW

I have an existing MATERIALIZED VIEW in my postgresql database. I am trying to create typeorm entity for that. Can anyone tell me how to do that. I already went through typeorm View Entities docs but it's not useful as I expected.
Jai Prak
  • 2,855
  • 4
  • 29
  • 37
8
votes
2 answers

Index to find records where the foreign key does not exist

Table products: id int primary_key Table transactions: product_id int references products The below SQL query is very slow: SELECT products.* FROM products LEFT JOIN transactions ON ( products.id = transactions.product_id…
samol
  • 18,950
  • 32
  • 88
  • 127
7
votes
3 answers

What's the difference between compiling and refreshing a materialized view?

We just ran into a problem where materialized views weren't refreshing, and giving a compile error. One of the senior developers says he just figured out how to fix it -- by telling toad to compile the materialized view. So my question is pretty…
jldugger
  • 2,339
  • 6
  • 22
  • 24
7
votes
2 answers

Does MySQL view always do full table scan?

I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour? The view is just a SELECT "All Columns From These Tables - NOT *"…
Alex
  • 34,776
  • 10
  • 53
  • 68
7
votes
2 answers

BigQuery materialized view - last in group

In BigQuery Is it possible to create a materialized view containing the latest row for each group in a base table. e.g. CREATE TABLE basetable ( group_id INT64, timestamp TIMESTAMP, value FLOAT64 ); INSERT INTO basetable (group_id, timestamp,…
Juri Krainjukov
  • 732
  • 8
  • 27
7
votes
2 answers

Need defense against wacky challenge to Event Sourcing architecture w/CosmosDB

In the current plan, incoming commands are handled via Function Apps, resulting in Events being sent to an Event Hub, and then materializing the views Someone is arguing that instead of storing events in something like table storage, and…
Michael J
  • 71
  • 2
1 2
3
…
55 56