I am having problems using Materialized Views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: I am having problems using Materialized Views

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001

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

  2. #2
    Join Date
    Sep 2001
    Posts
    37

    Re: I am having problems using Materialized Views

    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?

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Here is an example of a materialized view that we are creating:

    Code:
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    are you collecting good statistics on the MV and the base table? What queries are you submitting that are not getting rewritten?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2001
    Posts
    37
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    use the View DBA_MVIEW_ANALYSIS
    to detect problems with MViews.
    Orca

  7. #7
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi, to analyze query/rewrite-problems
    you can use :

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

    Orca

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    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.
    Last edited by gandolf989; 02-27-2003 at 10:19 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width