A type of view in SQL-Server that has materialized indexes and statistics.
0
votes
1answer
355 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 ...
1
vote
1answer
97 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 ...
2
votes
1answer
57 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 ...
0
votes
0answers
88 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
vote
2answers
95 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) ...
2
votes
1answer
92 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 ...
4
votes
1answer
88 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 ...
3
votes
1answer
114 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.
...
3
votes
1answer
168 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
203 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 ...
6
votes
2answers
202 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 ...
5
votes
1answer
589 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 ...
3
votes
1answer
189 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, ...
0
votes
1answer
29 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 ...
7
votes
1answer
386 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 ...
5
votes
2answers
452 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 ...
-3
votes
1answer
210 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 ...
2
votes
0answers
269 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
1answer
420 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 ...
19
votes
7answers
4k 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 ...
7
votes
2answers
1k 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 ...
2
votes
1answer
386 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 ...
1
vote
1answer
2k 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 ...
2
votes
1answer
129 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 ...
1
vote
0answers
23 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 ...
3
votes
1answer
100 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 ...
2
votes
2answers
693 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.
...
1
vote
2answers
608 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 ...
2
votes
2answers
777 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
..
)
, ...
1
vote
1answer
265 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
585 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
336 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
votes
1answer
778 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 ...
5
votes
1answer
3k 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 ...
2
votes
2answers
501 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 ...
5
votes
2answers
630 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)
, ...
