In data warehouse environments, it is common to use materialized views to speed up the performance of commonly used queries. A materialized view is simply a pre-built query that stores the results of the query in a table. With the query rewrite capability enabled, Oracle can automatically make use of materialized views without the user even knowing. Let’s take a look at a simple order table for a generic store along with a denormalized table for dates in the order system and some sample data:
CREATE TABLE dim_dates
(date_id DATE,
date_desc VARCHAR2(100),
month_id NUMBER,
month_desc VARCHAR2(100),
year_id NUMBER,
year_desc VARCHAR2(100),
CONSTRAINT dim_dates_pk PRIMARY KEY (date_id));
CREATE TABLE fact_sales
(date_id DATE,
user_id NUMBER,
product_id NUMBER,
amount NUMBER,
CONSTRAINT fact_sales_r01 FOREIGN KEY (date_id)
REFERENCES dim_dates (date_id));
INSERT INTO dim_dates
SELECT dte,
TO_CHAR(dte, 'DD MONTH YYYY'),
TO_CHAR(dte, 'YYYYMM'),
TO_CHAR(dte, 'MONTH YYYY'),
TO_CHAR(dte, 'YYYY'),
TO_CHAR(dte, 'YYYY')
FROM
(SELECT DISTINCT
TRUNC(SYSDATE, 'YEAR') + MOD(rownum, 1000) dte
FROM all_objects);
INSERT INTO fact_sales
SELECT TRUNC(SYSDATE, 'YEAR') + MOD(rownum, 1000),
MOD(rownum, 100),
MOD(rownum, 10),
ABS(DBMS_RANDOM.random)/1000
FROM all_objects;
BEGIN
dbms_stats.gather_table_stats(USER,'DIM_DATES',CASCADE=>TRUE);
END;
/
BEGIN
dbms_stats.gather_table_stats(USER,'FACT_SALES',CASCADE=>TRUE);
END;
/Now, supposed from our knowledge of the business, that users look at the sales by month much more often than by day. So we create a materialized view to aggregate this information:
CREATE MATERIALIZED VIEW agg_sales_monthly
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT d.month_id, f.product_id, SUM(f.amount)
FROM fact_sales f, dim_dates d
WHERE f.date_id = d.date_id
GROUP BY d.month_id, f.product_id;
BEGIN
dbms_stats.gather_table_stats(USER,'AGG_SALES_MONTHLY',CASCADE=>TRUE);
END;
/Now, even if a user does not know about the materialized view, Oracle can make use of it for faster query results (Note: make sure the system or session parameter query_rewrite_enabled = true to allow for rewrites):
EXPLAIN PLAN FOR
SELECT d.month_id, SUM(f.amount)
FROM fact_sales f, dim_dates d
WHERE f.date_id = d.date_id
GROUP BY d.month_id;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1473209833
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 4 |
| 1 | HASH GROUP BY | | 33 | 4 |
| 2 | MAT_VIEW REWRITE ACCESS FULL| AGG_SALES_MONTHLY | 330 | 3 |
--------------------------------------------------------------------------However, Oracle cannot make use of this aggregate if the wants to look at the data at a different level of aggregation, such as the year:
EXPLAIN PLAN FOR
SELECT d.year_id, SUM(f.amount)
FROM fact_sales f, dim_dates d
WHERE f.date_id = d.date_id
GROUP BY d.year_id;
PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 588016919
---------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 56|
| 1 | HASH GROUP BY | | 3 | 56|
|* 2 | HASH JOIN | | 57272 | 52|
| 3 | TABLE ACCESS FULL| DIM_DATES | 1000 | 4|
| 4 | TABLE ACCESS FULL| FACT_SALES | 57272 | 48|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."DATE_ID"="D"."DATE_ID")From the explain plan, it is clear that Oracle is using the base tables to execute this query. However, since year can be derived from month, Oracle should use the materialized view to return these results faster. With Oracle dimensions, we can inform Oracle about these relationships and allow the rewrite to occur:
CREATE DIMENSION dim_time
LEVEL day IS dim_dates.date_id
LEVEL month IS dim_dates.month_id
LEVEL year IS dim_dates.year_id
HIERARCHY time_rollup
(day CHILD OF
month CHILD OF
year);That dimension lets Oracle know how the time element can be aggregated, which can then be used to form the execution plan of the query. So if we try the year aggregate again:
EXPLAIN PLAN FOR
SELECT d.year_id, SUM(f.amount)
FROM fact_sales f, dim_dates d
WHERE f.date_id = d.date_id
GROUP BY d.year_id;
PLAN_TABLE_OUTPUT
---------------------------------------------------------
Plan hash value: 588016919
---------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 56|
| 1 | HASH GROUP BY | | 3 | 56|
|* 2 | HASH JOIN | | 57272 | 52|
| 3 | TABLE ACCESS FULL| DIM_DATES | 1000 | 4|
| 4 | TABLE ACCESS FULL| FACT_SALES | 57272 | 48|
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."DATE_ID"="D"."DATE_ID")The rewrite is still not occurring? That’s because Oracle has a default setting for query_rewrite_integrity = enforced. That setting means that rewrites will only occur on relationships that are enforced by constraints. By changing the system or session value for that parameter to trusted, Oracle can make use of relationships that are known, but not explicitly enforced.
ALTER SESSION SET query_rewrite_integrity = trusted;
EXPLAIN PLAN FOR
SELECT d.year_id, SUM(f.amount)
FROM fact_sales f, dim_dates d
WHERE f.date_id = d.date_id
GROUP BY d.year_id;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 4091167185
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 10|
| 1 | HASH GROUP BY | | 3 | 10|
|* 2 | HASH JOIN | | 710 | 9|
| 3 | VIEW | | 71 | 5|
| 4 | HASH UNIQUE | | 71 | 5|
| 5 | TABLE ACCESS FULL | DIM_DATES | 1000 | 4|
| 6 | MAT_VIEW REWRITE ACCESS FULL| AGG_SALES_MONTHLY | 330 | 3|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 – access("from$_subquery$_005"."MONTH_ID"="AGG_SALES_MONTHLY"."MONTH_ID")In conclusion, by using materialized views and dimensions, Oracle can perform some advanced rewrites automatically, which leads to much better performance in your data warehouse environment.
Link to script that contains the examples in this post.