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
A materialized cache with aggregate data can improve the performance of queries on analytic views. The analytic view automatically to redirects queries from the detailed fact table to the materialized cache.

To help the database choose the materialized cache, it is registered using CACHE clause.

The CACHE clause causes the analytic view to generate SQL that recognizes and uses the materialized cache. This can dramatically improve query performance. For example, a query that normally takes several seconds can be reduced to less than one second using a materialized cache.

This tutorial shows how to:
- Create a materialized cache on the fact data
- Add the CACHE clause to the analytic view definition
- Run queries with and without the materialized cache to compare performance
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 dataset includes 3 dimension tables and 1 fact table.

You can explore the data using the queries below.

TIME_DIM Table (used by TIME_HIER):

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;

PRODUCT_DIM Table (used by PRODUCT_HIER):

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

GEOGRAPHY_DIM Table (used by GEOGRAPHY_HIER):

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;

Sample Rows from SALES_FACT:

SELECT *  
FROM sales_fact  
WHERE ROWNUM <= 20;

Join Query: Aggregate Sales by Year, Department, and Region

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;

Preview Rows from 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 in an analytic view specifies how queries should be optimized for use with materialized views.

It includes:

MEASURE GROUP – List of measures that appear in the materialized cache.
LEVELS – List of dimension levels that match the GROUP BY columns in the materialized cache.

In Oracle 19c, the cache type is always MATERIALIZED.

A cache can support multiple LEVEL lists. Just repeat the LEVELS clause with different values, followed by the

MATERIALIZED

keyword each time.

Example: CACHE Clause in an Analytic View

This example matches the materialized cache 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  

-- Cache clause begins  
CACHE  
  MEASURE GROUP (sales, units)  
  LEVELS (  
    time_hier.year,  
    product_hier.department,  
    geography_hier.country)  
  MATERIALIZED;

View CACHE Clause Metadata

Use the following query to examine the cache properties:

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

Note: The analytic view is still queryable even if the materialized cache has not been created. The database will simply fall back to querying the detailed fact table.

Module 6
Creating an Aggregate Cache for the Analytic View
A materialized cache is implemented using a materialized view.

A materialized cache that supports an analytic view is simple and efficient.

It only needs to SELECT and GROUP BY the columns mapped to the key attributes of the target aggregate levels.

In this example, you'll create a materialized view that aggregates data to the:
- Year
- Department
- Country
This materialized cache will be used automatically by queries that select from the analytic view SALES_AV at these levels or higher.

Create Materialized Cache with DBMS_HIERARCHY

The DBMS_HIERARCHY.GET_MV_SQL_FOR_AV_CACHE procedure returns the SQL to build the materialized view.

Use the following PL/SQL block to create the materialized cache:
```
DECLARE
  cache_sql CLOB;
BEGIN<br>
  cache_sql := dbms_hierarchy.get_mv_sql_for_av_cache (
      analytic_view_name => 'SALES_AV',
      cache_idx => 0
  );
  EXECUTE IMMEDIATE
    'CREATE MATERIALIZED VIEW sales_av_cache_0 AS ' || cache_sql;
END;
/
```
Check the Materialized Cache

Verify that the materialized cache was created:
```
SELECT * FROM user_mviews;
```
To view the generated SQL:
```
SELECT TO_CHAR(dbms_hierarchy.get_mv_sql_for_av_cache (
  analytic_view_name => 'SALES_AV',
  cache_idx = 0
)) FROM dual;
```
Usage and Benefits

The materialized cache contains aggregate data at the specified levels. It is automatically used by the analytic view for queries at or above these levels.

Why is this more efficient?
- Shorter key values (e.g., IDs instead of text)
- No upper-level aggregates (like region names)
- Smaller size and faster to build
- Fits better in the in-memory column store
Enable Query Rewrite

To allow Oracle to rewrite queries to use the materialized cache:
```
ALTER MATERIALIZED VIEW sales_av_cache_0 ENABLE QUERY REWRITE;
```
Check Rewrite Status

Use this query to check the state of the materialized cache:
```
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
Notes on Query Rewrite to the Materialized View Cache

Keep the following in mind when enabling query rewrite for a materialized view used by an analytic view:
- Text-based matching: The analytic view uses text match to rewrite to the materialized view.
- No constraints required: Table constraints and SQL dimension objects are not needed for rewrite to work.
- Query must match: The defining query of the materialized view must exactly match the SQL returned by
  DBMS_HIERARCHY.GET_MV_SQL_FOR_AV_CACHE
  .
- Stale data handling: You can allow stale data by setting:
  
  ALTER SESSION SET query_rewrite_integrity = stale_tolerated;

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

TIME_DIM Table (used by TIME_HIER):

PRODUCT_DIM Table (used by PRODUCT_HIER):

GEOGRAPHY_DIM Table (used by GEOGRAPHY_HIER):

Sample Rows from SALES_FACT:

Join Query: Aggregate Sales by Year, Department, and Region

Preview Rows from Hierarchies:

Create Analytic View for Sales Data

Adding the CACHE clause to the Analytic View

Example: CACHE Clause in an Analytic View

View CACHE Clause Metadata

Creating an Aggregate Cache for the Analytic View

Create Materialized Cache with DBMS_HIERARCHY

Check the Materialized Cache

Usage and Benefits

Enable Query Rewrite

Check Rewrite Status

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