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
2 answers

What is the difference between complete refresh and fast refresh in materialized view?

I have materialized view in my own schema and the materialized view source is the other schema table but , this master table has 900 rows and i did some dml operations on this master table after that i will refresh this materialized view as you know…
MFARKAN
  • 226
  • 2
  • 3
  • 12
4
votes
1 answer

Domain index in materialized view return zero rows

I have problem with Oracle DB - domain index returns zero rows after search by CONTAINS() on materialized view. I see that materialized view is filled with data and I also used procedure ctx_ddl.sync_index() for domain index synchronization. What…
Martin
  • 61
  • 3
4
votes
1 answer

Relation between two tables with an optional third table in between

I have the following schema and data. --drop table table_c; --drop table table_b; --drop table table_a; create table table_a ( id number(3,0) primary key, value varchar2(10) ); create table table_b ( id number(3,0) primary key, …
Olivier Grégoire
  • 33,839
  • 23
  • 96
  • 137
4
votes
1 answer

Apache Cassandra 3.0.0 Materialized View: can the view's partition key change due to changes to the underlying table?

Just thinking about this so please correct my understanding if any of this isn't right. Environment: Apache Cassandra v3.0.0 Say you have a table and a materialized view created on it: create table source( id text, field text, stamp timestamp, data…
WillZ
  • 3,775
  • 5
  • 30
  • 38
4
votes
1 answer

Oracle: Is materialized view fast refresh atomic?

I have read quite a lot for past few hours about refreshing MV in Oracle, but I cannot still find an answer to my question. Imagine I have a MV view on top of a table with change logs. So that there are three records in this MV: COL_ID, COL1 1,…
wooki
  • 418
  • 5
  • 14
4
votes
1 answer

How do you run ActiveRecord `find_each` to a materialized view in postgres

Because I am pulling thousands of records from my materialized view Matview, I'd like to use find_each to break it up into batches to free up memory. However, when I run the following: Matview.where('p_id > 100').find_each {|m| m } I get the…
allenwlee
  • 665
  • 6
  • 21
4
votes
3 answers

Mapping entity to a materialized view using Hibernate

I need to map (PostgreSQL) a materialized view to @Entity, using Hibernate. If is hbm2ddl configured to update value, Hibernate always tries to create new SQL table. That only happens if the view is materialized, otherwise (with non-materialized…
Peter Jurkovic
  • 2,686
  • 6
  • 36
  • 55
4
votes
3 answers

Select distinct ... inner join vs. select ... where id in (...)

I'm trying to create a subset of a table (as a materialized view), defined as those records which have a matching record in another materialized view. For example, let's say I have a Users table with user_id and name columns, and a Log table, with…
Tonio
  • 1,516
  • 1
  • 15
  • 25
4
votes
1 answer

Refreshing an existing materialized View

I created a materialized view with the following information. CREATE MATERIALIZED VIEW EMPLOYEE_INFO AS SELECT * FROM EMPLOYEE_TABLE WHERE LOCATION = 'Brazil' I did not add any refresh interval to this MV initially. Now, I need to refresh this MV…
Cool_Oracle
  • 311
  • 1
  • 4
  • 15
4
votes
2 answers

Force Oracle database not to materialize CTE

I have some issue with cte, if I use this query oracle materialized the cte1 view and query will be slow with cte1 as (..), cte2 as ( ... use cte1 ...), cte3 as ( ... use cte1 ...) select * from cte2 join cte3 on ... in the following…
Tony
  • 2,266
  • 4
  • 33
  • 54
4
votes
2 answers

Does Cassandra provide Materialized Views

I have just started to use Cassandra DB. I want to create materialized views on Cassandra (to store queries) but, after seeking in google, I haven't found any information about it. Maybe it is not permitted in Cassandra? Well, materialized views in…
user3019292
  • 41
  • 1
  • 2
4
votes
1 answer

Transaction Performance Impact of Materialized View Logs

I have been researching using materialized views for data aggregation and reporting purposes for a company that is largely centered around transactions (using an Oracle db). The current reporting system is dependent upon a series of views that…
Daniel Heacock
  • 43
  • 1
  • 4
4
votes
2 answers

How to determine whether a table is used to store materialized view logs?

I have table with created on it materialized view log: create table T1(A number primary key); create materialized view log on T1 with primary key; Oracle additionally creates two tables for materialized view logs: select TABLE_NAME from…
turbanoff
  • 2,439
  • 6
  • 42
  • 99
4
votes
1 answer

Create new materialized view without the materialized view log being purged

I'd like to use materialized view on prebuilt table to keep a table in sync before a migration. The data is changing constantly, so the changes between export start and import finish need to be tracked. It goes without saying the table is huge so a…
kon5ad
  • 43
  • 1
  • 4
4
votes
2 answers

How to refresh materialized view using trigger?

create or replace TRIGGER REFRESH_REST_VIEW AFTER INSERT OR UPDATE ON tbl_contract BEGIN execute DBMS_MVIEW.REFRESH('REST_VIEW'); END REFRESH_REST_VIEW; commit; This is my sql trigger i am using to refresh Materialized View. But it…
manurajhada
  • 5,284
  • 3
  • 24
  • 43
1 2 3
…
55 56