A type of view in SQL-Server that has materialized indexes and statistics.

learn more… | top users | synonyms (1)

24
votes
7answers
7k views

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?

I am writing the schema for a simple bank database. Here are the basic specifications: The database will store transactions against a user and currency. Every user has one balance per currency, so ...
8
votes
2answers
862 views

Risks of changing to ARITHABORT ON

I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion ...
8
votes
2answers
343 views

DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition

TL;DR: I've got an unfixable corruption in an indexed view. Here the details: Running DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS on one of my ...
7
votes
1answer
772 views

What factors go into an Indexed View's Clustered Index being selected?

Breifly What factors go into they query optimizer's selection of an indexed view's index? For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this ...
7
votes
2answers
2k views

Limit redo for materialized view complete refresh or manual equivalent

A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore ...
6
votes
2answers
67 views

Need to include FKs in SQL Indexed View clustered index?

I'm using a SQL Server Indexed view to improve performance of a join that filters a (very) large table by a category on a small table. eg: CREATE VIEW BigTableSubset WITH SCHEMABINDING AS SELECT ...
6
votes
1answer
6k views

Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?

I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is ...
5
votes
2answers
188 views

Why do Indexed Views not permit non-unique clustered indexes?

I've been looking into using Indexed Views to increase performance on a few of our most commonly used views. However Indexed Views do not support non-unique clustered indexes which goes a little ...
5
votes
1answer
1k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
5
votes
2answers
796 views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
5
votes
2answers
717 views

Create index view with self join

I use SQL Server 2008 R2 and know that in Indexed View cant have self join. I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
4
votes
1answer
144 views

Materialized View PostgreSQL

Is there any possibility of having built-in materialized views in PostgreSQL ? I don't want Gardner version or somehthing like physical table populated by triggers. I've read that was/is something ...
4
votes
1answer
236 views

Adding indices to views

I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table. ...
4
votes
1answer
403 views

Materialized view: compilation_error

When executing the following code on the SCOTT schema I receive a compilation_error. What am I doing wrong here? I doesn't even tell me what the error is. SQL> create materialized view dept_emp_mv ...
4
votes
0answers
258 views

Howto improve performance for queries against table without primary key without changing the query or the table directly? [closed]

UPDATE: I included some updated information in this question, because there was no way of progressing here given the previous constraints. Scroll to the bold-faced paragraphs to see my updates; I ...
3
votes
1answer
543 views

How much of a view is persisted when you create an index?

Say I have a view defined by the following SQL: SELECT t1.id, t2.name, t3.address FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.id = t2.tID INNER JOIN Table3 t3 ON t1.id = t3.tID From what I ...
3
votes
2answers
399 views

Materialized view for the last 24 hour period

I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS ...
3
votes
1answer
120 views

Do operations on indexed views happen in parallel?

When I'm inserting into a table that has a view with a clustered index, the estimated execution plan appears to do the table insert and the view's clustered index insert operations in parallel. Is ...
3
votes
1answer
290 views

Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View?

My initial question was How does someone determine if it is a good idea to index a view?. Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, ...
3
votes
1answer
183 views

Dropped index from view still referenced in execution plans

On a SQL Server 2005 Enterprise edition server, an index has been dropped from a view. When running a select * query that includes this view, no results are shown. In addition, the execution plan ...
2
votes
1answer
185 views

Creating Indexed View GROUP BY Epoch Date

I have a few big tables with about 6 billion rows that I was looking to optimize. Clustered key is Epoch (unix date time which is the number of seconds that has passed after 1970) and customer ID. ...
2
votes
1answer
256 views

Speeding Up Querying Views

We've got a complicated stored procedure. The biggest pain point of the procedure is a join on a complicated view--it has unions and subqueries. I can't add indexes to it. So, I want to cheat. By ...
2
votes
2answers
1k views

WITH using INDEX hint in Oracle

I have a fairly complicated query. I have written two different query to accomplish my goal. First one is using WITH, other one is using global temporary tables. First One: WITH A ( KNO .. ) , ...
2
votes
1answer
275 views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
2
votes
1answer
127 views

Index will be removed

I'm trying to simply change the length of a column within a table through SQL Server Management Studio, but receive a 'Validation Warning': 'MyTable' table -Warning: The following schema-bound ...
2
votes
1answer
124 views

Updating column in underlying table causes massive transaction log growth

I have an underlying table ~14k records and an indexed view that contains ~11 million records. When updating a column in the small underlying table this seems to cause a massive transaction log entry ...
2
votes
1answer
625 views

Creating Bitmap Indexes on a Materialized View

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned: Also, remember that bitmap indexes are only suitable for static ...
2
votes
2answers
1k views

Materialized views use older dbms_job instead of scheduler?

Very new to this topic - using 11g. When I create a materialized view, a matching job (not a scheduler job) is created. But it's my understanding that dbms_job is being replaced by dbms_scheduler. ...
2
votes
1answer
183 views

Views - Indexes/Keys/Execution Plans

I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server. There is a ...
2
votes
2answers
706 views

Error updating a master table of a materialized view

I've created several materialized views, and all but one are working correctly. The specific error I'm getting (via a Java job) is this: error loading from table 'STG_INTERACTION_RESOURCE_FACT' to ...
2
votes
0answers
339 views

Issues with Indexed Views in SQL Server 2008r2 on a table with persisted columns and with low DML activity

I am issues with using indexed views. I have a product catalog and a stored proc reads the data in the rows according to various filters on the data. At the same time, the data needs to remain in XML ...
1
vote
2answers
1k views

Materialized View - Oracle

I have materialized view for which i want to alter its refresh time: REFRESH FORCE START WITH SYSDATE NEXT SYSDATE +1 DISABLE QUERY REWRITE I want to know few things in this. What does NEXT ...
1
vote
2answers
194 views

Database Design question, calculate or save financial positions

As the question states i'm currently internally strugling witha database design question. I work as a software developer (and i tend to do everything database related aswell since we're rather small) ...
1
vote
1answer
3k views

Materialized view taking too much time

i create a materialized view fast refresh. it takes almost 45 min to create but it did not refresh in 24 hours. i tried it on with index and without index. i check the log of all table max record in ...
1
vote
1answer
663 views

Indexed View Slows Replication

I have setup SQL Server transactional replication, and then added indexed views at the subscriber, but the indexed views appear to really slow down the subscriber and almost bringing it to a halt. I ...
1
vote
1answer
351 views

Oracle Materialized View

I need your help with a issue on Oracle Materialized View. I created the Materialized View using the following command: CREATE MATERIALIZED VIEW SNAP_TEST TABLESPACE tbs NOCACHE LOGGING NOPARALLEL ...
1
vote
1answer
492 views

Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent?

I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future. What is the meaning of DBA_SUMMARIES ...
1
vote
1answer
608 views

Immediate refresh materialized views (MQT) with OUTER JOIN under DB2

DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE). I cannot find a simple solution to get round this problem. Perhaps with ...
1
vote
1answer
726 views

How to drop materialized view logs if underlying log table ($MLOG_…) is dropped?

In an attempt to work around another problem, I have mistakenly dropped the underlying tables for a materialized view log, and now I can neither drop that log nor the MV relating to it. Our DBA says ...
1
vote
1answer
103 views

One Materialized Views in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
1
vote
0answers
29 views

Monoblock, Multiblock queries?

What exactly mean the terms monoblock / multiblock queries in data warehouses when talking about materialized views? Does a monoblock query just use one data source (a normal table) while a multiblock ...
0
votes
1answer
40 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
629 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
0answers
90 views

Howto improve performance for these queries, why is my indexed view making it worse for most of them and how can I change that?

for the record, this is a rewrite of the following question (you do not have to read that because I include all important data here) Howto improve performance for queries against table without primary ...
0
votes
0answers
131 views

Materialized view for a yet-to-be-defined DBlink

In our product we have a main application with an oracle 11g DB and an optional module with another DB. We want the two to communicate through a dblink and I was told to put some code for the ...
-1
votes
1answer
1k 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 ...
-4
votes
1answer
429 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 ...