Can you create a multiple-table aggregate materialized views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Can you create a multiple-table aggregate materialized views

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    Can you create a multiple-table aggregate materialized views

    Can you create a materialized view on mulitple tables and using aggregates?
    I have created the logs on each table with rowid and including new values.

    This is the create statement:
    1 create materialized view test_sds
    2 parallel
    3 build immediate
    4 refresh fast on commit
    5 as
    6 SELECT TO_CHAR(BILLPLED_POSTED_DATE,'MM') MONTH,RPAD(AIRLTABL_CODE_ICAO_2, 5) || ' - ' || AIRLTABL_DESCR AIRLINE
    7 , SUM(Getledgervalue(AIRLTABL_CODE_ICAO_2
    8 , BILLREBT_CODE
    9 , BILLPLED_POSTED_DATE
    10 , BILLPLED_POSTED_DATE)) VALUE
    11 , BILLREBT_GROUP_SUMMARY
    12 FROM AIRLTABL, BILLREBT, BILLPLED
    13 WHERE AIRLTABL_CODE_ICAO_2 = BILLPLED_OPERATOR
    14 ---AND BILLPLED_ACTUAL_DATE BETWEEN datFrom AND datTo
    15 AND BILLREBT_GROUP_SUMMARY IS NOT NULL
    16 AND BILLPLED_BILLCHGCODE_CODE = BILLREBT_CODE
    17 AND BILLPLED_CREDIT > 0
    18* GROUP BY TO_CHAR(BILLPLED_POSTED_DATE,'MM'),RPAD(AIRLTABL_CODE_ICAO_2, 5) || ' - ' || AIRLTABL_DESCR,BILLREBT_GROUP_SUMMARY

    and this is the error:
    FROM AIRLTABL, BILLREBT, BILLPLED
    *
    ERROR at line 12:
    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    If not is this achievable in Oracle 9i?
    Thanks.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Code:
    Restrictions on Fast Refresh on Materialized Views with Aggregates
    Defining queries for materialized views with joins and aggregates have the following restrictions on fast refresh:
    
    All restrictions from "General Restrictions on Fast Refresh". 
    Fast refresh is supported for both ON COMMIT and ON DEMAND materialized views, however the following restrictions apply:
    
    All tables in the materialized view must have materialized view logs, and the materialized view logs must: 
    -----> Contain all columns from the table referenced in the materialized view. 
    Specify with ROWID and INCLUDING NEW VALUES. 
    Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates. 
    Only SUM, COUNT, AVG, STDDEV, VARIANCE, MIN and MAX are supported for fast refresh. 
    COUNT(*) must be specified. 
    For each aggregate AGG(expr), the corresponding COUNT(expr) must be present. 
    If VARIANCE(expr) or STDDEV(expr) is specified, COUNT(expr) and SUM(expr) must be specified. Oracle recommends that SUM(expr *expr) be specified. See Table 8-1 for further details. 
    The SELECT list must contain all GROUP BY columns. 
    If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads. 
    Materialized views with MIN or MAX aggregates 
    Materialized views which have SUM(expr) but no COUNT(expr) 
    Materialized views without COUNT(*) 
    Such a materialized view is called an insert-only materialized view.
    
    The COMPATIBILITY parameter must be set to 9.0 if the materialized aggregate view has inline views, outer joins, self joins or grouping sets and FAST REFRESH is specified during creation. Note that all other requirements for fast refresh specified previously must also be satisfied. 
    Materialized views with named views or subqueries in the FROM clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, refer to the Oracle9i Database Performance Tuning Guide and Reference. 
    If there are no outer joins, you may have arbitrary selections and joins in the WHERE clause. 
    Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator. 
    For materialized views with CUBE, ROLLUP, Grouping Sets, or concatenation of them, the following restrictions apply: 
    The SELECT list should contain grouping distinguisher that can either be a GROUPING_ID function on all GROUP BY expressions or GROUPING functions one for each GROUP BY expression. For example, if the GROUP BY clause of the materialized view is "GROUP BY CUBE(a, b)", then the SELECT list should contain either "GROUPING_ID(a, b)" or "GROUPING(a) AND GROUPING(b)" for the materialized view to be fast refreshable. 
    GROUP BY should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)".

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I had a big argument with Oracle Support over this.

    There are examples of doing exactly this sort of thing in the Data Warehousing guide, but they said that they couldn't help me with my problem (which was actually to do with getting a fast refresh) because this was not a supported methodology.

    Anyway, as pando points out there are some severe restrictions that you have to respect and which depend on your version number. I would wonder whether that user-defined function might be a problem, myslef.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

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