Using the Analytic View Materialized Aggregate Cache

View All Tutorials Login to Run Tutorial

Tutorial Using the Analytic View Materialized Aggregate Cache
Description This tutorial shows how to use materialized views to accelerate queries that select from analytic views.
Tags analytic view, materialized view, aggregate, cache
Area SQL Analytics
Contributor William Endress (Oracle)
Created Wednesday January 04, 2017
Modules 12

Prerequisite SQL

-- Copy table to current schema in order to allow ALTER MATERIALIZED VIEW ... ENABLE QUERY REWRITE without
-- granting additional privileges.

CREATE TABLE time_dim AS SELECT * FROM av.time_dim;
CREATE TABLE product_dim AS SELECT * FROM av.product_dim;
CREATE TABLE geography_dim AS SELECT * FROM av.geography_dim;
CREATE TABLE sales_fact AS SELECT * FROM av.sales_fact;

-- Create attribute dimensions and hierarchies that will be used by the SALES_AV analytic view.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING time_dim
ATTRIBUTES
 (year_id
   CLASSIFICATION caption VALUE 'YEAR_ID'
   CLASSIFICATION description VALUE 'YEAR ID',
  year_name
    CLASSIFICATION caption VALUE 'YEAR_NAME'
    CLASSIFICATION description VALUE 'Year',
  year_end_date
    CLASSIFICATION caption VALUE 'YEAR_END_DATE'
    CLASSIFICATION description VALUE 'Year End Date',
  quarter_id
    CLASSIFICATION caption VALUE 'QUARTER_ID'
    CLASSIFICATION description VALUE 'QUARTER ID',
  quarter_name
    CLASSIFICATION caption VALUE 'QUARTER_NAME'
    CLASSIFICATION description VALUE 'Quarter',
  quarter_end_date
    CLASSIFICATION caption VALUE 'QUARTER_END_DATE'
    CLASSIFICATION description VALUE 'Quarter End Date',
  month_id
    CLASSIFICATION caption VALUE 'MONTH_ID'
    CLASSIFICATION description VALUE 'MONTH ID',
  month_name
    CLASSIFICATION caption VALUE 'MONTH_NAME'
    CLASSIFICATION description VALUE 'Month',
  month_long_name
    CLASSIFICATION caption VALUE 'MONTH_LONG_NAME'
    CLASSIFICATION description VALUE 'Month Long Name',
  month_end_date
    CLASSIFICATION caption VALUE 'MONTH_END_DATE'
    CLASSIFICATION description VALUE 'Month End Date')
LEVEL month
  CLASSIFICATION caption VALUE 'MONTH'
  CLASSIFICATION description VALUE 'Month'
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (month_end_date,
    quarter_id)
LEVEL quarter
  CLASSIFICATION caption VALUE 'QUARTER'
  CLASSIFICATION description VALUE 'Quarter'
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (quarter_end_date,
    year_id)
LEVEL year
  CLASSIFICATION caption VALUE 'YEAR'
  CLASSIFICATION description VALUE 'Year'
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date
  DETERMINES (year_end_date)
ALL MEMBER NAME 'ALL TIMES';

CREATE OR REPLACE HIERARCHY time_hier
  CLASSIFICATION caption VALUE 'CALENDAR'
  CLASSIFICATION description VALUE 'CALENDAR'
USING time_attr_dim
 (month CHILD OF
 quarter CHILD OF
 year);

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING product_dim 
ATTRIBUTES
 (department_id
    CLASSIFICATION caption VALUE 'DEPARTMENT_ID'
    CLASSIFICATION description VALUE 'DEPARTMENT ID',
  department_name
    CLASSIFICATION caption VALUE 'DEPARTMENT_NAME'
    CLASSIFICATION description VALUE 'Department',
  category_id
    CLASSIFICATION caption VALUE 'CATEGORY_ID'
    CLASSIFICATION description VALUE 'CATEGORY ID',
  category_name
    CLASSIFICATION caption VALUE 'CATEGORY_NAME'
    CLASSIFICATION description VALUE 'Category')
LEVEL DEPARTMENT
  CLASSIFICATION caption VALUE 'DEPARTMENT'
  CLASSIFICATION description VALUE 'Department'
  KEY department_id
  MEMBER NAME department_name
  MEMBER CAPTION department_name
  ORDER BY department_name
LEVEL CATEGORY
  CLASSIFICATION caption VALUE 'CATEGORY'
  CLASSIFICATION description VALUE 'Category'
  KEY category_id
  MEMBER NAME category_name
  MEMBER CAPTION category_name
  ORDER BY category_name
  DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';

CREATE OR REPLACE HIERARCHY product_hier
  CLASSIFICATION caption VALUE 'PRODUCT'
  CLASSIFICATION description VALUE 'Product'
USING product_attr_dim
 (CATEGORY
  CHILD OF department);

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING geography_dim
ATTRIBUTES
 (region_id
    CLASSIFICATION caption VALUE 'REGION_ID'
    CLASSIFICATION description VALUE 'REGION ID',
  region_name
    CLASSIFICATION caption VALUE 'REGION_NAME'
    CLASSIFICATION description VALUE 'Region',
  country_id
    CLASSIFICATION caption VALUE 'COUNTRY_ID'
    CLASSIFICATION description VALUE 'COUNTRY ID',
  country_name
    CLASSIFICATION caption VALUE 'COUNTRY_NAME'
    CLASSIFICATION description VALUE 'Country',
  state_province_id
    CLASSIFICATION caption VALUE 'STATE_PROVINCE_ID'
    CLASSIFICATION description VALUE 'STATE-PROVINCE ID',
  state_province_name
    CLASSIFICATION caption VALUE 'STATE_PROVINCE_NAME'
    CLASSIFICATION description VALUE 'State-Province')
LEVEL REGION
  CLASSIFICATION caption VALUE 'REGION'
  CLASSIFICATION description VALUE 'Region'
  KEY region_id
  MEMBER NAME region_name
  MEMBER CAPTION region_name
  ORDER BY region_name
LEVEL COUNTRY
  CLASSIFICATION caption VALUE 'COUNTRY'
  CLASSIFICATION description VALUE 'Country'
  KEY country_id
  MEMBER NAME country_name
  MEMBER CAPTION country_name
  ORDER BY country_name
  DETERMINES(region_id)
LEVEL STATE_PROVINCE
  CLASSIFICATION caption VALUE 'STATE_PROVINCE'
  CLASSIFICATION description VALUE 'State-Province'
  KEY state_province_id
  MEMBER NAME state_province_name
  MEMBER CAPTION state_province_name
  ORDER BY state_province_name
  DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';

CREATE OR REPLACE HIERARCHY geography_hier
  CLASSIFICATION caption VALUE 'GEOGRAPHY'
  CLASSIFICATION description VALUE 'Geography'
USING geography_attr_dim
 (state_province
  CHILD OF country
  CHILD OF region);

-- Plan table.

CREATE TABLE PLAN_TABLE ( 
        statement_id       varchar2(30), 
        plan_id            number, 
        timestamp          date, 
        remarks            varchar2(4000), 
        operation          varchar2(30), 
        options            varchar2(255), 
        object_node        varchar2(128), 
        object_owner       varchar2(30), 
        object_name        varchar2(30), 
        object_alias       varchar2(65), 
        object_instance    numeric, 
        object_type        varchar2(30), 
        optimizer          varchar2(255), 
        search_columns     number, 
        id                 numeric, 
        parent_id          numeric, 
        depth              numeric, 
        position           numeric, 
        cost               numeric, 
        cardinality        numeric, 
        bytes              numeric, 
        other_tag          varchar2(255), 
        partition_start    varchar2(255), 
        partition_stop     varchar2(255), 
        partition_id       numeric, 
        other              long, 
        distribution       varchar2(30), 
        cpu_cost           numeric, 
        io_cost            numeric, 
        temp_space         numeric, 
        access_predicates  varchar2(4000), 
        filter_predicates  varchar2(4000), 
        projection         varchar2(4000), 
        time               numeric, 
        qblock_name        varchar2(30), 
        other_xml          clob);

Module 1
Introduction
Materialized views with aggregate level data can be used to accelerate queries that select from an analytic view. The database uses automatic query rewrite to redirect the query from the detailed fact table to the materialized view. In order to encourage the database to rewrite queries to the materialized view, the analytic view can generate SQL that is materialized view aware.

The CACHE clause is used to cause the analytic view to generate materialized view aware SQL. The effect on query performance can be significant. For example, a query that selects aggregate level data from a large fact table might be reduced from many seconds to less than a second when a materialized view is use.

This tutorial provides an example of using a materialized view using the AV sample schema. You will create a materialized view, add the CACHE clause to the definition of an analytic view and query the analytic view with and without the materialized view.
Module 2
Setup
If you have not already done so, click Execute the SQL required by this tutorial which is before the list of modules in this tutorial. The setup script will copy tables from the AV schema into the current schema and create attribute dimensions and hierarchies for times, products and geographies.

You can check that these objects have been created using the following queries.

Attribute dimensions.
```
SELECT * FROM user_attribute_dimensions;
```
Hierarchies.
```
SELECT * FROM user_hierarchies;
```

Module 3

Reviewing the Sample Data

The sample data includes 3 dimension tables and 1 fact table.

You can review the data using the following queries.

Select the columns from the TIME_DIM table that are referenced by the TIME_HIER hierarchy.

SELECT month_id,
  month_name,
  month_long_name,
  month_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  year_id,
  year_name,
  year_end_date
FROM time_dim
ORDER BY month_end_date;

Select the columns from the PRODUCT_DIM table that are referenced by the PRODUCT_HIER hierarchy.

SELECT category_id,
  category_name,
  department_id,
  department_name
FROM product_dim
ORDER BY category_name;

Select the columns from the GEOGRAPHY_DIM table that are referenced by the GEOGRAPHY_HIER hierarchy.

SELECT state_province_id,
  state_province_name,
  country_id,
  country_name,
  region_id,
  region_name
FROM geography_dim
ORDER BY region_name,
  country_name,
  state_province_name;

Select a sample of rows from the SALES_FACT table;

SELECT * FROM sales_fact WHERE rownum <= 20;

The following query selects aggregate level data from the dimension and fact tables. This query illustrates the joins between the tables.

SELECT
  t.year_name,
  p.department_name,
  g.region_name,
  SUM(f.sales) AS sales,
  SUM(f.units) AS 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_name,
  p.department_name,
  g.region_name
ORDER BY 
  t.year_name,
  p.department_name,
  g.region_name;

You can use the following queries to select from the hierarchies.

SELECT * FROM time_hier WHERE rownum <= 20;

SELECT * FROM product_hier WHERE rownum <= 20;

SELECT * FROM geography_hier WHERE rownum <= 20;

Module 4

Create Analytic View for Sales Data

The following CREATE ANALYTIC VIEW statement creates an analytic view without 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;

The following query selects the data at the Year, Department and Region levels.

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;

The SQL execution plan will show that the SALES_FACT table is being accessed.

Write the plan to the plan table.

TRUNCATE TABLE plan_table;
EXPLAIN PLAN
SET STATEMENT_ID = '1' 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_FACT table is accessed. Note that the query of the plan table filters for ACCESS operations.

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

Module 5

Adding the CACHE clause to the Analytic View

The CACHE clause includes a MEASURE GROUP list and a LEVELS list. In Oracle 19c the cache type is always MATERIALIZED. The measures and levels should match with key attribute columns in the materialized view. A cache can support multiple LEVEL lists. Simply repeat the LEVEL list (with different values) and the MATERIALIZED keyword.

The CACHE clause in the following analytic view matches the materialized view created in the previous module.

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 attributes 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;

You can see the analytic view CACHE clause properties with the following query.

SELECT
  analytic_view_name,
  cache_type,
  hier_alias,
  level_name,
  measure_name
FROM user_analytic_view_lvlgrps;

Although the cache has yet to be created, the analytic view may still be queried. The database will recognize the cache doesn't exist and access the detail tables.

Module 6
Creating an Aggregate Cache for the Analytic View
A materialized view that supports an analytic view is very simple and efficient - it only needs to SELECT and GROUP BY the columns mapped to the key attributes of appropriate the aggregate levels. In this tutorial you will make a materialized view that aggregates data to the Year, Department and Country levels. This materialized view can be used by any query selecting from the analytic view at these levels or above.

The DBMS_HIERARCHY.GET_MV_SQL_FOR_AV_CACHE procedure returns a query that can be used to create an aggregate table or a materialized view. The following PL/SQL code creates an aggregate table.
```
DECLARE
  cache_sql CLOB;
BEGIN
  cache_sql := dbms_hierarchy.get_mv_sql_for_av_cache (
              analytic_view_name => 'SALES_AV'
               , cache_idx => 0    -- VALUE FROM THE PREVIOUS QUERY
            );

  EXECUTE IMMEDIATE
    'CREATE MATERIALIZED VIEW sales_av_cache_0 AS ' || cache_sql;

END;
/
```
Check that the materialized view was created.
```
SELECT * FROM user_mviews;
```
If you want to see the SQL, you run the following SELECT statement.
```
SELECT TO_CHAR(dbms_hierarchy.get_mv_sql_for_av_cache (
    analytic_view_name => 'SALES_AV'
    , cache_idx => 0 ))
    FROM dual;
```
The table contains aggregate data at the specified levels. The analytic view will access this table for any query at or above the levels of the materialized cache.

Note that this query, which selects relatively short key values and does not include upper level aggregate columns (for example, a column for region), is likely to be much smaller than the typical query (with text descriptor columns and upper level aggregate columns) used in a materialized view. This materialized view will be quicker to build, use less disk space and more easily fit into the in-memory column store than a typical materialized view supporting a star schema.

Enable the materialized view for query rewrite.
```
ALTER MATERIALIZED VIEW sales_av_cache_0 ENABLE QUERY REWRITE;
```
You can check the state of the SALES_AV_CACHE_0 materialized view with the following query.
```
SELECT
  mview_name,
  rewrite_capability,
  staleness,
  rewrite_enabled
FROM user_mviews
WHERE mview_name = 'SALES_AV_CACHE_0';
```
Module 7
Notes About Query Rewrite to the Materialized Cache
A few important notes about rewrite to the materialized view cache:
- The analytic view uses text match to rewrite to the materialized view.
- Neither table constraints or SQL dimension objects are required for rewrite from the analytic view to the materialized view.
- The defining query of the materialized view much match the query generated by DBMS_HIERARCHY.GET_MV_SQL_FOR_AV_CACHE.
- ALTER SESSION SET query_rewrite_integrity = stale_tolerated is supported, allowing you to choose whether or not a materialized view used by the materialized cache is allowed to access a stale materialized view.

Module 8

Querying the Analytic View

When the CACHE clause is added to the analytic view and there is a matching, fresh, rewrite enabled materialized view the database will automatically rewrite SQL generated by the analytic view to the materialized view. No hints are required.

The following query aggregates data to the same levels of the materialized view (Year, Department and Country). Write the SQL execution plan to the plan table.

<p>Enable the materialized view for query rewrite.</p>

<code>ALTER MATERIALIZED VIEW sales_av_cache_0 ENABLE QUERY REWRITE;

You can check the state of the SALES_AV_CACHE_0 materialized view with the following query.

SELECT
  mview_name,
  rewrite_capability,
  staleness,
  rewrite_enabled
FROM user_mviews
WHERE mview_name = 'SALES_AV_CACHE_0';

EXPLAIN PLAN SET STATEMENT_ID = '3' 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_0 table is accessed using query rewrite.

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

The next query access data above the materialized view (Year, Department and Region levels). Write the plan to the plan table.

TRUNCATE TABLE plan_table;
EXPLAIN PLAN
SET STATEMENT_ID = '4' 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_CACHE_0 table is accessed using query rewrite

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

The database can rewrite the query to the materialized view when there are a subset of hierarchies included in the query. In this example the query uses only the TIME_HIER and PRODUCT_HIER hierarchies. Write the plan to the plan table.

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

Query the plan table. Note that the SALES_AV_CACHE_0 table is accessed via query rewrite and that there is further aggregation with GROUP BY.

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

Module 9

About the State of the MV and Query Rewrite

The analytic view respects the state of materialized view and query rewrite parameters, just like any other query.

The materialized view must be enabled for query rewrite.
Query rewrite must be enabled (e.g., ALTER SESSION ENABLE QUERY REWRITE;).
The materialized by must be fresh or QUERY_REWRITE_INTEGRITY must be TRUE.

The following update statement will cause the SALES_AV_CACHE_0 materialized view to become stale.

UPDATE sales_fact
SET units = 100
WHERE month_id = 'Apr-11'
  AND category_id = '-535'
  AND state_province_id = 'ALBERTA_CA';
COMMIT;

Note that the materialized view is now stale.

SELECT
  mview_name,
  rewrite_capability,
  staleness,
  rewrite_enabled
FROM user_mviews
WHERE mview_name = 'SALES_AV_CACHE_0';

Examine the SQL execution plan and note that the query does not rewrite to the materialized view.

EXPLAIN PLAN
SET STATEMENT_ID = '6' 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;

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

Alter the session QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;

Run the SQL execution plan again. Note that the query will now rewrite to the materialized view.

EXPLAIN PLAN
SET STATEMENT_ID = '7' 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;

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

Module 10

Using Multiple Aggregate Caches

You can use multiple aggregate caches with an analytic view by including multiple LEVELS groups in the CACHE clause.

The first aggregate cache included data the the Year, Department and Country levels. The following statement add a second LEVELS group at the Year, Department, and Region levels..

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 aggregate cache.
      MEASURE GROUP (
          sales,
          units)
        LEVELS (
          -- List of levels in the aggregate cache.
          time_hier.year,
          product_hier.department,
          geography_hier.country)
        -- Indicates a materialized view.  On Autonomous Database or 23c,
        -- MATERIALIZED USING table_name directly accesses a table.
        MATERIALIZED
        LEVELS (
          -- List of levels in the aggregate cache.
          time_hier.year,
          product_hier.department,
          geography_hier.region)
        -- Indicates a materialized view.  On Autonomous Database or 23c,
        -- MATERIALIZED USING table_name directly accesses a table.
        MATERIALIZED;

You can see the analytic view CACHE clause properties with the following query. Note that the new cache is CACHE_IDX 1.

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;

Create the materialized view for CACHE_IDX 1.

DECLARE
  cache_sql CLOB;
BEGIN
  cache_sql := dbms_hierarchy.get_mv_sql_for_av_cache (
              analytic_view_name => 'SALES_AV'
               , cache_idx => 1    -- VALUE FROM THE PREVIOUS QUERY
            );

  EXECUTE IMMEDIATE
    'CREATE MATERIALIZED VIEW sales_av_cache_1 AS ' || cache_sql;

END;
/

Check that the materialized view was created.

SELECT * FROM user_mviews;

If you want to see the SQL, you run the following SELECT statement.

SELECT TO_CHAR(dbms_hierarchy.get_mv_sql_for_av_cache (
    analytic_view_name => 'SALES_AV'
    , cache_idx => 1 ))
    FROM dual;

Enable the materialized view for query rewrite.

ALTER MATERIALIZED VIEW sales_av_cache_1 ENABLE QUERY REWRITE;

You can check the state of the SALES_AV_CACHE_1 materialized view with the following query.

SELECT
  mview_name,
  rewrite_capability,
  staleness,
  rewrite_enabled
FROM user_mviews
WHERE mview_name = 'SALES_AV_CACHE_1';

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

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_CACHE_0 table is accessed

SELECT LPAD('............................',2*(LEVEL-1)) ||operation operation,
  OPTIONS,
  object_name,
  position
FROM plan_table
WHERE operation like '%ACCESS%'
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_CACHE_1 table is accessed

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

Module 11
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.
Module 12
Questions or Comments?
Get in touch at william.endress@oracle.com.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

Using the Analytic View Materialized Aggregate Cache

Prerequisite SQL

Introduction

Setup

Reviewing the Sample Data

Create Analytic View for Sales Data

Adding the CACHE clause to the Analytic View

Creating an Aggregate Cache for the Analytic View

Notes About Query Rewrite to the Materialized Cache

Querying the Analytic View

About the State of the MV and Query Rewrite

Using Multiple Aggregate Caches

Implementation Tips

Questions or Comments?

Additional Information