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
3
votes
1 answer

Enabling fast refresh on materialized view with geometry

I have a seemingly simple problem, for which materialized views seem to be the ideal solution, however I do not get it to work efficiently, and maybe the answer simply is: "Oracle does not allow it at all", but I am hoping I am overlooking something…
nathanvda
  • 49,707
  • 13
  • 117
  • 139
3
votes
0 answers

How to make SQLAlchemy custom DDL be emitted after object inserted?

I have a PostgreSQL Materialized View that calculates some data about Manufacturers. I created a SQLAlchemy custom DDL command to refresh the view: from sqlalchemy.schema import DDLElement from sqlalchemy.ext import compiler class…
Jeff Widman
  • 22,014
  • 12
  • 72
  • 88
3
votes
2 answers

Can't set the ON COMMIT refresh attribute when creating a materialized view containing partial primary key in Oracle

I need to extract the unique values of a column which is part of the primary key from a table into a materialized view. I can create the materialized view if using "refresh complete" but with no luck when trying to use "refresh fast on commit". Can…
Jason
  • 146
  • 2
  • 7
3
votes
3 answers

"Missing table" on materialized view

I have the following entity: @Entity @Table(name = "full_address") public class FullAddress { @Id @Column(name = "guid") private String id; @Column(name = "address") private String address; //getters and setters omitted } Also I…
Everv0id
  • 1,862
  • 3
  • 25
  • 47
3
votes
1 answer

Can I set up materialised views on oracle for a SQL Server table over sqllink?

I have a table in a SQL Server database that needs to be visible to an oracle database. We have tried using a normal view over sqllink, but we are not able to create an onUpdate triggers on that view. I have read that we can create the trigger if it…
iWantSimpleLife
  • 1,944
  • 14
  • 22
3
votes
4 answers

MySQL VIEW vs. embedded query, which one is faster?

I'm going to optimize a MySQL embedded query with a view, but I'm not sure whether it will give an effect: SELECT id FROM (SELECT * FROM t); I want to convert it to: CREATE VIEW v AS SELECT * FROM t; SELECT id FROM v; I've heard about "indexed…
yegor256
  • 102,010
  • 123
  • 446
  • 597
3
votes
1 answer

Oracle: Select parallel entries

I am searching the most efficient way to make a relatively complicated query in a relatively large table. The concept is that: I have a table that holds records of phases that can run parallel to each other The amount of records exceeds the 5…
Stef
  • 143
  • 1
  • 10
3
votes
1 answer

SQL Server indexed view matching of views with joins not working

Does anyone have experience of when SQL Server 2008 R2 is able to automatically match indexed view (also known as materialized views) that contain joins to a query? For example the view select dbo.Orders.Date, dbo.OrderDetails.ProductID from…
usr
  • 168,620
  • 35
  • 240
  • 369
3
votes
1 answer

What is the relation between a materialized view and the eponymous table?

I have an oracle DB with a materialized view that is populated from another oracle DB and refreshed every 2 minutes using DBMS_REFRESH.REFRESH(). I just noticed that I have a table with the same name. Does this table contain the exact same data as…
Roland
  • 7,525
  • 13
  • 61
  • 124
3
votes
2 answers

materialized view use case

I have been learning about materialized views and for what they are used/can be used. However i dont find how it would be useful , since it doesnt contain current data because it takes time to refresh/update. Also looking at real life scenarios it…
avinashkr
  • 512
  • 1
  • 5
  • 20
3
votes
2 answers

Scripted Conversion of Views to Materialized Views (Oracle)

We have certain environments that prefer the use of materialized views, but the regular applications use regular views. To make things easier we want our application to automatically migrate all the regular views to materialized views in an…
mrswadge
  • 1,659
  • 1
  • 20
  • 43
3
votes
0 answers

Best approach for heavy read queries in reports using JPA?

I'm developing a REST WebService, and for the data layer I'm using JPA with Hibernate. My application requires many different reports over a lot of data. I believe that querying this data using regular queries (being them JPQL or Native SQL) will…
miguelcobain
  • 4,734
  • 4
  • 32
  • 45
3
votes
0 answers

Error in materialized view refresh path / permission

I have a materalized view I've been using for quite awhile now where I perform a fast refresh every night prior to another job. The PROC gets called through an automated system (Active Batch), and contains the following refresh: …
denisb
  • 787
  • 5
  • 18
  • 31
3
votes
3 answers

Oracle materialized view using with "not exists"

I know Oracle materialized views cannot be fast refreshed with "not exists" clause. Is there a work around. I tried using left outer join and (+) but these 2 options too didnt seem to work. Any help is appreciated create materialized view mv_myview…
Thunderhashy
  • 5,291
  • 13
  • 43
  • 47
3
votes
2 answers

Comparing Date column to sysdate yields: a non-numeric character was found where a numeric was expected

I've been having a strange issue where the comparison of a date column to SYSDATE yields the following error: 01858. 00000 - "a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date…
sonar
  • 385
  • 3
  • 15
1 2 3
…
55 56