I am having problems using Materialized Views
I am working on a data warehouse which has a tool that we use for creating tables and mapping columns from imports. We are currently writing code for 9iSR2 on most unix variants, i.e. Sun, AIX and HP, and maybe Linux. The data modeling portion of the tool has revisions it also tracks changes made to each revision. So when we go to pull out data we need to specify the primary key columns, the revision and then find the latest update_date_time for that set of values.
table t1 has the following primary keys: pkey1, pkey2, pkey3, revision and update_date_time. We end up doing a lot of subqueries with the max(update_date_time) function to return the correct update_date_time and thus the current record. This is compounded by the fact that we don't update or delete we insert new records on top of old records. We do delete when we archive revisions, to counter my previous statemwnt.
I have been asked to research using Materialized views to find the correct update_date_time and thus speed up the program. The problem is that I am not able to create a materialized view works with the queries that I am using. I can't get Oracle to rewrite the queries to use the materialized views.
I am looking for any insights that people have concerning whether materialized views are appropriate for aggragate functions and this scenario in particular.
When I write queries against the materialized views directly I have found that the performance is worse than writing the queries against a plain view and in some cases it was worse than not using any kind of view at all. Any ideas on what I might be doing wrong would be appreciated. For the records query rewrite is set to true in the database.
Re: I am having problems using Materialized Views
Quote:
Originally posted by gandolf989
The problem is that I am not able to create a materialized view works with the queries that I am using. I can't get Oracle to rewrite the queries to use the materialized views.
To enable a materialized view for query rewrite, all of the master tables for it must be in the materialized view's schema, and you must have the QUERY REWRITE system privilege. If the view and the tables are in separate schemas you must have GLOBAL QUERY REWRITE system privilege.
Quote:
Originally posted by gandolf989 ??
When I write queries against the materialized views directly I have found that the performance is worse than writing the queries against a plain view and in some cases it was worse than not using any kind of view at all.
What's the refresh type & interval of your MV's?