I am having problems using Materialized Views

[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.