materialized-views

What is the difference between Views and Materialized Views in Oracle?

What is the difference between Views and Materialized Views in Oracle? ...

Is it possible to have an indexed view in MySQL?

I found a posting on the MySQL forums from 2005, but nothing more recent than that. Based on that, it's not possible. But a lot can change in 3-4 years. What I'm looking for is a way to have an index over a view but have the table that is viewed remain unindexed. Indexing hurts the writing process and this table is written to quite freq...

oracle materialized view refresh time

hey all, anyone able to tell me how often a materialized view is set to refresh with the following setting plz? REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT (round(sysdate) + 1/24) + 1 i think i read it as every hour but i'm not sure ...

Does informix have a "materialized view" equivalent or DB-table syncing

Question: Does Informix have a construct equivalent to Oracle's "materialized view" or is there a better way to synchronize two tables (not DB's) accross a DB link? I could write a sync myself (was asked to) but that seems like re-inventing the wheel. Background: Recently we had to split (one part of DB one one server, the other part o...

Does MySQL view always do full table scan?

I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour? The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below. This is my explain output from...

Oracle materialized view tuning

I am trying to perform tuning of materialized views in my application. I set out to execute: dbms_advisor.tune_mview() procedure, and wrote the following code into an SQL_Worksheet of Oracle SQL Developer: variable mvtask varchar2(100); variable mvddl varchar2(4000); execute :mvtask := 'MV_FOO_BAR'; execute select query into :mvdd...

Why does DBMS_MVIEW.REFRESH have an implicit commit?

I noticed recently that calling dbms_mview.refresh(...), which refreshes materialized views in Oracle, has an implicit commit. Any ideas - other than "because it does" - why this action has an implicit commit? ...

How to test materialized view in java

Is there any way to write automated tests for materialied views using Hibernate framework? I don't know how to make it work. My first approach was to use DBMS_MVIEW.REFRESH stored procedure. But, it is not what I need - all test data is commited after refreshing the view. Is there any different way to test the view? ...

Oracle Materialized Views Vs Replication on the same DB server

We need to run reports at a scheduled time of the day. The application runs 24*7 and so there is not "off-peak" time as such. Therefore, running the reports should not add undue load on the system. The application runs on WebSphere v6.1 and the database is Oracle 10g R2. I have the following approaches at my disposal A set of de-nor...

Performance of Remote Materialized views in Oracle

I have a question reagrding Oracle materialized views ... We have two databases: Core Database Reporting database The reporting database has: a database link to the Core database a number of synonyms to the tables in the Core database a number of Materialized views defined on top of those synonyms. The views are set up to refres...

MySQL not using index with JOIN, WHERE and ORDER

We have two tables resembling a simple tag-record structure as follows (in reality it's much more complex but this is the essance of the problem): tag (A.a) | recordId (A.b) 1 | 1 2 | 1 2 | 2 3 | 2 .... and recordId (B.b) | recordData (B.c) 1 | 123 2 | 666 3 | 124...

How can a materialized view be created in sqlite?

I've done countless searches on materialized views and SQLite. Of what I can find there seems to be mentions in 2004 and 2006 that SQLite DOES NOT have materialized views. Followed immediately by SQLite's changelog from March 2008 where it specifically mentions optimizing materialized views. Now, I figure logically either the 2004 and...

What can cause a materialized view in Oracle 10g to stop fast refreshing?

If I have materialized view in Oracle which is defined as REFRESH FAST ON COMMIT every 15 minutes. It works when initially created and refreshes happily. What can cause it to stop fast refreshing? I can see that it has stopped refreshing based on this: select mview_name, last_refresh_date from all_mviews; ...

CREATE TABLE AS SELECT killing MySQL

We run a MySQL server with moderate load (200-300 QPS) on quite powerful hardware (HP DL360 with 8 Xeon cores, 8Gb RAM and RAID10). All the tables are innodb and the active dataset fits within the allocated innodb_buffer_pool_size. Our database is normalized and to reduce the number of joins we use materialized views to flatten the data...

Query/Database Optimization: How to optimize this? (and should I use a materialized view?)

Hi -- I have a question on how to optimize a query. Actually, as I'm going to be running the query frequently, I was thinking of using a materialized or indexed view (is this a good idea here?) or denormalizing. Consider the following four tables (with irrelevant fields omitted): Users (int userId) Groups (int groupId) GroupMembershi...

Help needed for optimizing linq data extraction

I'm fetching data from all 3 tables at once to avoid network latency. Fetching the data is pretty fast, but when I loop through the results a lot of time is used Int32[] arr = { 1 }; var query = from a in arr select new { Basket = from b in ent.Basket where b.SUPERBASKETID == ...

Materialized view with Oracle

Hello ! I have the following line in a script and I don't understand what the "using" part is used for. I couldn't find anything on google. Anybody familiar with that ? Thanks a lot !! CREATE MATERIALIZED VIEW "PVTRNDM"."DM_MVW_DAILY_CAL" USING ("DM_MVW_DAILY_CAL", (8, 'PLANVP.XXXX.INT', 1, 0, 0, "PVTRN", "DAILY_CAL", '2009-...

Materialized View with column aggregate

This is another stab into a problem I posted here. Please don't close as duplicate, because it goes in another direction. I'd like to automatically update a database column with an aggregate of another column. There are three tables involved: T_RIDER RIDER_ID TMP_PONYLIST ... T_RIDER_PONY RIDER_ID PONY_ID T_PONY PONY_ID ...

Is it possible to partially refresh a materialized view in Oracle?

I have a very complex Oracle view based on other materialized views, regular views as well as some tables (I can't "fast refresh" it). Most of the time, existing records in this view are based on a date and are "stable", with new record sets having new dates. Occasionally, I receive back-dates. I know what those are and how to deal wit...

How can I tell if a Materialized View in Oracle is being used?

We have some Materialized views in our Oracle 9i database that were created a long time ago, by a guy no longer working here. Is there an easy (or any) method to determine whether Oracle is using these views to serve queries? If they aren't being used any more, we'd like to get rid of them. But we don't want to discover after the fact...