Using Materialized Views With Analytic Views

  • Module 9

    Using Multiple Materialized Views

    The first materialized view included data the the Year, Department and Country levels. Create a new materialized view with data at the Year, Department and Regions levels.

    CREATE MATERIALIZED VIEW sales_av_mv_2
    AS
    SELECT
      t.year_id,
      p.department_id,
      g.region_id,
      SUM(f.sales) AS measures#sales,
      SUM(f.units) AS measures#units
    FROM
      time_dim t,
      product_dim p,
      geography_dim g,
      sales_fact f
    WHERE
      t.month_id = f.month_id
      AND p.category_id = f.category_id
      AND g.state_province_id = f.state_province_id
    GROUP BY
      t.year_id,
      p.department_id,
      g.region_id;

    Enable the materialized view for query rewrite.

    ALTER MATERIALIZED VIEW sales_av_mv_2 ENABLE QUERY REWRITE;

    Add a second LEVELS group using the Region level to the CACHE clause.

    CREATE OR REPLACE ANALYTIC VIEW sales_av
    USING sales_fact
    DIMENSION BY
      (time_attr_dim
        KEY month_id REFERENCES month_id
        HIERARCHIES (
          time_hier DEFAULT),
       product_attr_dim
        KEY category_id REFERENCES category_id
        HIERARCHIES (
          product_hier DEFAULT),
       geography_attr_dim
        KEY state_province_id 
        REFERENCES state_province_id
        HIERARCHIES (
          geography_hier DEFAULT)
       )
    MEASURES
     (sales FACT sales,
      units FACT units,
      sales_prior_period AS
        (LAG(SALES) OVER (HIERARCHY time_hier OFFSET 1)),
      sales_share_prod_parent AS
       (SHARE_OF(sales HIERARCHY product_hier PARENT)),
      sales_share_geog_parent AS
       (SHARE_OF(sales HIERARCHY geography_hier PARENT))
      )
    DEFAULT MEASURE SALES
        -- Start cache clause
        CACHE
          -- List of measures in the materailized view.
          MEASURE GROUP (
              sales,
              units)
            LEVELS (
              -- List of measures that matches the group by of the materialized view.
              time_hier.year,
              product_hier.department,
              geography_hier.country)
            -- Indicates a materialized view (no other cache types are currently supported).
            MATERIALIZED
            LEVELS (
              -- List of measures that matches the group by of the materialized view.
              time_hier.year,
              product_hier.department,
              geography_hier.region)
            -- Indicates a materialized view (no other cache types are currently supported).
            MATERIALIZED;

    SELECT
      analytic_view_name,
      av_lvlgrp_order,
      cache_type,
      hier_alias,
      level_name,
      measure_name
    FROM user_analytic_view_lvlgrps
    ORDER BY 
     av_lvlgrp_order,
     level_name NULLS LAST,
     hier_alias NULLS FIRST,
     measure_name NULLS FIRST;

    The following query selects at the Year, Department and Country. This query will rewrite to SALES_AV_MV.

    TRUNCATE TABLE plan_table;
    EXPLAIN PLAN
    SET STATEMENT_ID = '8' INTO plan_table FOR
    SELECT
      time_hier.member_name,
      product_hier.member_name,
      geography_hier.member_name,
      sales,
      units
    FROM
      sales_av HIERARCHIES (time_hier, product_hier, geography_hier)
    WHERE
      time_hier.level_name = 'YEAR'
      AND product_hier.level_name = 'DEPARTMENT'
      AND geography_hier.level_name = 'COUNTRY'
    ORDER BY 
      time_hier.member_name,
      product_hier.member_name,
      geography_hier.member_name;

    Query the plan table. Note that the SALES_AV_MV table is accessed using query rewrite.

    SELECT LPAD('............................',2*(LEVEL-1)) ||operation operation,
      OPTIONS,
      object_name,
      position
    FROM plan_table
    START WITH id       = 0
     AND statement_id      = '8'
     CONNECT BY PRIOR id = parent_id
     AND statement_id      = '8';

    The next query accesses data at the Year, Department and Region levels. Write the plan to the plan table.

    TRUNCATE TABLE plan_table;
    EXPLAIN PLAN
    SET STATEMENT_ID = '9' INTO plan_table FOR
    SELECT
      time_hier.member_name,
      product_hier.member_name,
      geography_hier.member_name,
      sales,
      units
    FROM
      sales_av HIERARCHIES (time_hier, product_hier, geography_hier)
    WHERE
      time_hier.level_name = 'YEAR'
      AND product_hier.level_name = 'DEPARTMENT'
      AND geography_hier.level_name = 'REGION'
    ORDER BY 
      time_hier.member_name,
      product_hier.member_name,
      geography_hier.member_name;

    Query the plan table. Note that the SALES_AV_MV_2 table is accessed using query rewrite.

    SELECT LPAD('............................',2*(LEVEL-1)) ||operation operation,
      OPTIONS,
      object_name,
      position
    FROM plan_table
    START WITH id       = 0
     AND statement_id      = '9'
     CONNECT BY PRIOR id = parent_id
     AND statement_id      = '9';

  • Module 10

    Implementation Tips

    Consider the following tips when using materialized views with analytic views.

    • Using integer keys for the key attributes in the attribute dimension and thus for the SELECT list in the defining query of the materialized view can result in a materialized view that is quicker to create, smaller and faster to query as compared to using longer text values.
    • If possible, use Database In-Memory for both the fact table and the materialized view. A materialized view will create much more quickly from a fact table that is in the in-memory column store. Include the VECTOR_TRANSFORM hint in the defining query of the materialized view to force the vector transform SQL execution plan (a.k.a. in-memory aggregation). Load the materialized view into the in-memory column store for faster query (e.g., ALTER TABLE sales_av_mv INMEMORY).
    • Generally speaking, queries at higher levels of aggregation benefit more from materialized views because those queries access and aggregate larger numbers of fact rows. Try a few different level groupings to see what works best.
    • Consider using more than one materialized view. For example, one MV for the top-most aggregates and another for mid-level aggregates. Include a CACHE clause for each MV. The analytic view will try to use the closest MV to a query.
  • Additional Information