I am having problems using Materialized Views

[gandolf989]

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.

[ccastaneda]

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.

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?

[gandolf989]

Here is an example of a materialized view that we are creating:

CREATE MATERIALIZED VIEW LOG ON iet$wa_application
	WITH PRIMARY KEY, ROWID
	INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW iemv$wa_application
   USING INDEX
   ENABLE QUERY REWRITE AS
	SELECT 
		application_id,
		revision,
		MAX(update_date_time)
	FROM iet$wa_application
	GROUP BY
		application_id,
		revision;

Also we have our databases with optimizer_mode set to choose, which I just found out requires us to maintain stats. Which if we changed it to either all_rows or first_rows stats might not be needed. I don't know why but I am continuing to look. As you can see we specify query rewrite in the MV as well as query rewrite is turned on in the database. We want to make sure that the MV's are always up to date so I'm not sure if the sql to create the MV's is correct.

[slimdave]

are you collecting good statistics on the MV and the base table? What queries are you submitting that are not getting rewritten?

[ccastaneda]

A MV Log applies only when the MV use queries that select rows only from only one table and don't use joins or aggregate functions (knowns as "Simple" queries in this context). Create them when for other types of queries is pointless because they won't be used by Oracle (Your query uses a GROUP BY for example)

You should create MV's in the same schema as the tables on wich they're based if you wanna enable query rewrite; otherwise you will need to manage the permissions and grants required to create and mantain the MV's (QUERY REWRITE or GLOBAL QUERY REWRITE)

You can increase performance creating indexes in the MV's and its base tables. I am not sure but i think that Oracle only create an index automatically for the MV if it was created with the Primary Key Option. Correct me ppl if i am wrong ppl. Other indexes that could improve performance must be created manually.

You must take a decision about the moment for the data's refresh of the MV's. It seems that your data comes from a batch process so you could refresh them after the upload. Check the DBMS_MVIEW or the DBMS_SNAPSHOT packages for further reference.

Regards

[Orca777]

Hi,
use the View DBA_MVIEW_ANALYSIS
to detect problems with MViews.
Orca

[Orca777]

Hi, to analyze query/rewrite-problems
you can use :

DBMS_MVIEW.EXPLAIN_REWRITE (
query IN VARCHAR2,
mv IN VARCHAR2,
statement_id IN VARCHAR2);

Orca

[gandolf989]

Originally posted by slimdave
are you collecting good statistics on the MV
and the base table? What queries are you submitting
that are not getting rewritten?

I am doing am analyze schema with a full compute.
Also everything is in the same schema. I am looking
at creating indexes on the MV's.


Orca77 I tried to use the package you mentioned.
I was looking for the package in enterprise manager
and was suprised to find out that DBMS_MVIEW is a
synonym for DBMS_SNAPSHOT. Unfortunatley I got an
error. I also tried to run it as sys. Any ideas?

1 DECLARE
2 myquery VARCHAR2(2000);
3 mymv VARCHAR2(200) := 'DATAMAP.IEMV$WA_DATA_STORE';
4 mystatementid VARCHAR2(200) := '666';
5 BEGIN
6 myquery := 'SELECT COUNT(*) FROM DATAMAP.iet$wa_data_store, DATAMAP.iet$wa_data_set '||
7 'WHERE iet$wa_data_store.data_store_seq = iet$wa_data_set.data_store_seq AND '||
8 'iet$wa_data_set.application_id = ''TEST'' AND '||
9 'iet$wa_data_set.data_store_name = ''TEST'' AND '||
10 'iet$wa_data_set.data_set_name = ''TEST'' AND '||
11 'iet$wa_data_set.revision = ''TEST'' AND '||
12 'iet$wa_data_set.update_date_time = ( '||
13 'SELECT MAX(update_date_time) '||
14 'FROM iet$wa_data_set ds1 '||
15 'WHERE ds1.data_set_seq = iet$wa_data_set.data_set_seq)';
16 DBMS_MVIEW.EXPLAIN_REWRITE(myquery, mymv, mystatementid);
17* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-30380: REWRITE_TABLE does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1434
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 1514
ORA-06512: at line 16

Thanks.