1 Reply Latest reply: Aug 30, 2012 1:52 AM by 791901 RSS

    Materialized views - query rewrite --> optional joins & dimensions

    791901
      Hi,

      I've implemented a number of materialized views that are accessible via query rewrite.
      Most of these views make use of duplicate tables (DD_TIME table is joined via purchase, load, process, ... date columns)

      I've also created a dimension to make Oracle aware of the time relation (month is child of year...)
      The issue:
      --> If I create the mat. view with inner joins, query rewrite works with the dimensions, but I need to provide ALL joined tables in the query
      --> If I create the mat. view with outer joins, query rewrite works even with 1 joined table, but the dimension seems to be ignored

      How can I create 'optional' joins in the Mat. View while making use of dimensions for rollup?
      (the limitations on materialized view delta joins mentioned on [oracle docs|http://docs.oracle.com/cd/B28359_01/server.111/b28313/qradv.htm#autoId16] kinda has me worried)

      Example Mat. View:
      CREATE MATERIALIZED VIEW DA_REPOSITORY_MERCHANT_MM
      ... ENABLE QUERY REWRITE
      AS SELECT
      DDFPC.YEAR_NO,
      DDFPC.YEAR_MONTH,
      DDPUR.YEAR_NO,
      DDPUR.YEAR_MONTH,
      SUM( DF.TRANSACTION_AMOUNT )
      FROM DF_REP DF,
      DD_TIME DDFPC,
      DD_TIME DDPUR
      WHERE DF.FPC_DTE = DDFPC.DAY_DATE(+)
      AND DF.PUR_DTE = DDPUR.DAY_DATE(+)
      GROUP BY
      DDFPC.YEAR_NO,
      DDFPC.YEAR_MONTH,
      DDPUR.YEAR_NO,
      DDPUR.YEAR_MONTH

      Example query launched:
      SELECT
      DDFPC.YEAR_NO,
      SUM( DF.TRANSACTION_AMOUNT )
      FROM DF_REP DF,
      DD_TIME DDFPC
      WHERE DF.FPC_DTE = DDFPC.DAY_DATE
      GROUP BY
      DDFPC.YEAR_NO
        • 1. Re: Materialized views - query rewrite --> optional joins & dimensions
          791901
          The problem was resolved by setting foreign keys in the fact table and making them not nullable + using inner joins.
          --> this informs oracle there can be no difference between a query without the dimensions and a query with the dimensions.

          As a result, the materialized view can contain 1 fact linked to 2 dimension tables, but the query rewrite will work for queries on the 1 fact joined with only 1 dimension