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.

Filter by
Sorted by
Tagged with
0
votes
1answer
1k views

Materialized View for SQL Server

I want to know if there is some thing similar to materialized view for Oracle in SQL Server . Any ideas ?
0
votes
1answer
1k views

How do I copy a materialized view to a table?

I have a materialized view. I want to create a copy of it so I can modify some of the data to test a few theories about an issue and its possible solutions. How do I make a copy of this MV as a table?...
0
votes
1answer
58 views

Oracle scheduler chain priority in data warehouse applications

The scenario is as follows: We have a data warehouse type application that relies on the Oracle job scheduler and a Chain. This chain is responsible for generating materialized views (caches) that ...
0
votes
1answer
4k views

Problem with dropping materialized views and mv logs taking a long time (or never) to complete

Apologies if it's a simple question, DBA is not my main area of expertise! I have a materialized view that's fast refreshable, joining two tables. When I try to drop either the MV itself or logs ...
0
votes
1answer
76 views

Getting ORA-32042 even though i can create the view in SQL Developer

I have a strange problem creating a materialized view using the code generated by the export wizard. I have created a materialized view and pasted following code into the sql query tab from the popup....
0
votes
1answer
400 views

Is using indexed view with no expand on standard edition a must?

I have recently read the text below in 70-762 Developing SQL Databases (p. 67): The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the ...
0
votes
1answer
403 views

Create a materialized view on commit with PIVOT function

I'm trying to create a materialized view with on commit refresh while using a PIVOT on it's select. I have the following tables: Events: represents an event that the system process. It's like a ...
0
votes
1answer
595 views

Export Materialized View from postgresql as a table?

I have a materialized view I created in Postgresql. I was wondering is there a way to pg_dump the materialized view as if they were tables, so when I import them into another database it sees it as ...
0
votes
1answer
68 views

SQL Indexed View for Datamart Reporting with Transactional Replication

Should Indexed Views be utilized/recommended as method to auto-generate DataMart Reporting tables? We have a Customer timeline of tables which can be simply joined on CustomerId in each tables. ...
0
votes
1answer
610 views

Oracle - Foreign Key on non Unique - is materialized view the answer?

we have the following table to store texts in different languages: CREATE TABLE TXT ( TXTID VARCHAR2(10 BYTE) NOT NULL , LANGUAGE VARCHAR2(2 BYTE) DEFAULT 'DE' NOT NULL , STEXT VARCHAR2(10 BYTE) ...
0
votes
1answer
45 views

Multiple table constraint in oracle

A table of boxes, with a capacity column, and a table of items with columns box and size, and you want to enforce the constraint that an item can't be put in a box of capacity smaller than the item. ...
0
votes
3answers
18k views

How to know when a refresh of a materialized view has been completed

After a specific event(e.g. someone add new data into the database via a GUI), I need to refresh a materialized view that aggregates some data and only after that refresh is complete I have to query ...
0
votes
1answer
215 views

Inheritance and performance in database [closed]

I have 3 table linked by an "inheritance" with Entity Framework. Sadly, I have poor performance with those query because I can't make any good index. Here is the tables : CREATE TABLE [dbo].[...
0
votes
1answer
235 views

On a single table, how is an indexed view different from a non clustered index?

Assuming that no joins are involved, and the PK of the view (the UNIQUE Clustered Index) is the same as that of the base table, is there a difference between how a nonclustered index (NCL) and an ...
0
votes
1answer
4k views

How to view the original script that built a materialized view and that was created by another user?

In the Oracle database server use, another user (called MIMIC2DEVEL) created a materialized view and made it public. How to view the original script that was used to built it? Here are the grants on ...
0
votes
1answer
4k views

Does MariaDB Support Materialized views? [duplicate]

Before I even try to experiment with MariaDB, could anyone tell me if MariaDB supports Materialized views? I can't seem to find it in their documentation. This is one of my major issues with MySQL (...
0
votes
1answer
183 views

The materialized view is already optimal and cannot be tuned any further

I have tried DBMS_ADVISOR.TUNE_MVIEW to verify some materialized views created locally. One of the result returns an error: Error report: ORA-13600: error encountered in Advisor QSM-03116: The ...
0
votes
1answer
3k views

Materialized views master table

New to this topic - in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by ...
0
votes
0answers
25 views

Materialized view bigger than table it is based on?

I have a database that's ~130 GB and one table makes up for most of this (85 GB). Because this table holds a lot of duplicates, we have a materialized view which basically uses a window function to ...
0
votes
0answers
62 views

Do Hive Materialized Views respond to added partitions to source tables?

Hive Materialized Views support partitions, CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name [DISABLE REWRITE] [COMMENT materialized_view_comment] [PARTITIONED ON (...
0
votes
0answers
47 views

Create indexed view with fluctuating data behind

I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are ...
0
votes
0answers
34 views

mysql run out of memory during mview refresh

I want to create mview from mysql to Oracle using DBLink via odbc driver with Linux 64 bit os. but due to large number of Records around 2.5 cr records it raise the error ERROR at line 1: ORA-28500: ...
0
votes
0answers
33 views

Is it possible to execute a postgresql function when any view is refreshed?

I want to alert my application that are utilizing views when the view is refreshed. Is this possible using PostgreSQL built-in functionality? If not are there any other approaches to tackle this?
0
votes
0answers
15 views

Ιndexed view after replication

I am trying to find a smart (no redundunt dataflow) way to aggregate data on views/tables to be used from our reporting tools. One given is that the reporting database is different from the oltp one, ...
0
votes
1answer
62 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
0
votes
1answer
221 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
0
votes
1answer
196 views

oracle-mysql fast refresh materialized view

Is is possible to Create Fast refresh enabled Materialized view from MySQL to Oracle . i know steps for oracle to oracle Fast refresh enabled Materialized view. But now the scenario is need to ...
0
votes
1answer
183 views

Create or refresh materialized view

How would you create new or otherwise refresh a materialized view in Postgres? I've tried something like this: CREATE OR REFRESH MATERIALIZED VIEW institution_mv AS SELECT entity_id, ...
0
votes
0answers
46 views

In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't

I am using SQL Server 2016-SP1-CU8 Here is the code to setup my scenario: --1. Drop indexed views (in case of re-running IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1 IF ...
0
votes
1answer
3k views

Creating a Materialized view with fast refresh and on Demand option

create materialized view XXARG_GOKU AS select XDR.GLOBAL_ID XDR.PARTY_ID, XDR.EMAIL_ADDRESS, XDU.CITY_CODE, XDU.STD_CODE, XDU.PIN_CODE_SERIAL_NO from XXARG_DUMMY XDU, XXARG_DRAGON XDR where XDR....
0
votes
1answer
6k views

How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status

I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH. I've MV with this PL/SQL : CREATE MATERIALIZED VIEW "...
0
votes
0answers
407 views

index of aggregate value with fast update?

Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire ...
0
votes
0answers
389 views

Is it possible to create materialized view based on another materialized view in postgres?

I have a very large table and I summarize it in a materialized view. I then create an even smaller materialized view based on the earlier mview. The second mv is always showing zero rows. Is there a ...
0
votes
1answer
291 views

Can there be situations where using (noexpand) on an indexed view is undesirable?

From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well. Apparently using the indexed ...
-1
votes
1answer
2k views

Create materialized view no refresh from existing view with index

I've already got a working db view : CREATE OR REPLACE FORCE VIEW "USER_DATA_VIEW" ("APP_USER_ID", "FIRST_NAME", "LAST_NAME", "FULL_NAME", "LOCATION_ID", "LOCATION_NAME") AS SELECT U.APP_USER_ID, ...
-1
votes
0answers
30 views

How indexed view slow down the performance of DML operations on the tables used in indexed view itself? [on hold]

I would like to get some detailed knowledge about the impact of indexed view on the performance of DML statements on the tables in the indexed view.
-3
votes
1answer
1k views

Why does a VIEW with a CLUSTERED index not use the index?

I have a view that has a clustered index along with other non-clustered indexes. A simple SELECT query does not use any of the indexes even when I select an indexed column. What could be the ...
-3
votes
1answer
191 views

Optimizing an indexed view used for selects to also do updates

I have a materialized indexed view that returns ~2.6 million of the ~2.7 million rows in the table, causing the optimizer to mostly use clustered index scans. If I do an update on this view will it ...