Home » Developer & Programmer » Data Integration » Usage of Query Rewrite in Materialized Views
Usage of Query Rewrite in Materialized Views [message #92854] Wed, 07 November 2001 21:32 Go to next message
sagar
Messages: 11
Registered: December 2000
Junior Member
Hi,

I have a star schema with fact table and and dimensions tables. One of the dimension tables is time_dimension and I have created a materialized view(time_sales_mv) on it and the fact table. I have also created a dimension(time_dim) on the table 'time_dimension' with hierarchies and attributes. Following are the syntaxes -

--Dimension table
CREATE TABLE TIME_DIMENSION (
TIME_KEY NUMBER(9) NOT NULL,
DAY_OF_MONTH NUMBER(9),
WEEKDAY NUMBER(9),
WEEKEND NUMBER(9),
JULIAN_DAY NUMBER(9),
JULIAN_WEEK NUMBER(9),
JULIAN_YEAR NUMBER(9),
MONTH_NUMBER NUMBER(9),
MONTH_NAME VARCHAR2(15),
WEEK_OF_THE_YEAR NUMBER(9),
WEEKDAY_NAME VARCHAR2(10),
WEEK_DAY_NUMBER NUMBER(9),
THE_YEAR NUMBER(9),
DAY_OF_THE_YEAR NUMBER(9),
THE_DATE DATE,
THE_QUARTER NUMBER(9),
PRIMARY KEY ( TIME_KEY )) ;

--Fact table
CREATE TABLE SALES_FACT (
TIME_KEY NUMBER(9) NOT NULL,
PRODUCT_KEY NUMBER(9) NOT NULL,
PROMOTION_KEY NUMBER(9) NOT NULL,
CUSTOMER_KEY NUMBER(9) NOT NULL,
DOLLAR_SALES FLOAT,
UNIT_SALES NUMBER(9),
DOLLAR_COST FLOAT)

-- Dimension created
CREATE DIMENSION Time_dim
LEVEL THE_DATE IS TIME_DIMENSION.THE_DATE
LEVEL WEEK_OF_THE_YEAR IS time_dimension.WEEK_OF_THE_YEAR
LEVEL MONTH_NUMBER IS time_dimension.MONTH_NUMBER
LEVEL THE_QUARTER IS time_dimension.THE_QUARTER
LEVEL THE_YEAR IS time_dimension.THE_YEAR
HIERARCHY calendar_rollup (
THE_DATE CHILD OF
MONTH_NUMBER CHILD OF
THE_QUARTER CHILD OF
THE_YEAR )
HIERARCHY weekly_rollup (
THE_DATE CHILD OF
WEEK_OF_THE_YEAR )
ATTRIBUTE THE_DATE DETERMINES time_dimension_sagar.DAY_OF_MONTH
ATTRIBUTE THE_DATE DETERMINES time_dimension_sagar.WEEKDAY
ATTRIBUTE THE_DATE DETERMINES time_dimension_sagar.JULIAN_DAY
ATTRIBUTE THE_DATE DETERMINES time_dimension_sagar.DAY_OF_THE_YEAR
ATTRIBUTE MONTH_NUMBER DETERMINES time_dimension_sagar.month_name
ATTRIBUTE THE_YEAR DETERMINES time_dimension_sagar.JULIAN_YEAR;

-- Materialized View
CREATE MATERIALIZED VIEW time_sales_mv
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT t.month_number, SUM(dollar_sales) AS sum_dollar_sales FROM sales_fact s,time_dimension t
WHERE t.time_key = s.time_key GROUP BY t.month_number

Now if I use the same query as in the MV and see the explain plan it shows the MV is being used instead of the underlying tables which is as expected. But if I change 'month_number' to 'month_name' in the above query, the explain plan does not use the MV which is not as expected. Since 'month_name' is an attribute of 'month_number'(defined in the dimension definition), we can use it and query rewrite feature will join the MV to the time_dimension table. But in the actual plan, it uses the fact table 'sales_fact' instead of the MV. Even when I use the rewrite hint on the query it does not use the MV. I want know why this is happening??

Query-
SELECT t.month_number, SUM(dollar_sales) AS sum_dollar_sales FROM sales_fact s, time_dimension t
WHERE t.time_key = s.time_key
GROUP BY t.month_number

Explain Plan -
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=2132)
TABLE ACCESS (FULL) OF TIME_SALES_MV (Cost=1 Card=82 Bytes=2132)

Query(using month_name instead of month_number)-

SELECT t.month_name, SUM(dollar_sales)
FROM sales_fact s, time_dimension t
WHERE t.time_key = s.time_key
GROUP BY t.month_name

Explain Plan -
SELECT STATEMENT Optimizer=CHOOSE (Cost=151 Card=9053 Bytes=307802)
SORT (GROUP BY) (Cost=151 Card=9053 Bytes=307802)
HASH JOIN (Cost=16 Card=9053 Bytes=307802)
TABLE ACCESS (FULL) OF TIME_DIMENSION_SAGAR (Cost=1 Card=82 Bytes=1804)
TABLE ACCESS (FULL) OF SALES_FACT (Cost=10 Card=11040 Bytes=132480)

Query (using rewrite hint in the above query) -

SELECT /*+ rewrite(time_sales_mv)*/ t.month_name, SUM(dollar_sales)
FROM sales_fact s, time_dimension t
WHERE t.time_key = s.time_key
GROUP BY t.month_name

Explain Plan -
SELECT STATEMENT Optimizer=CHOOSE (Cost=151 Card=9053 Bytes=307802)
SORT (GROUP BY) (Cost=151 Card=9053 Bytes=307802)
HASH JOIN (Cost=16 Card=9053 Bytes=307802)
TABLE ACCESS (FULL) OF TIME_DIMENSION_SAGAR (Cost=1 Card=82 Bytes=1804)
TABLE ACCESS (FULL) OF SALES_FACT (Cost=10 Card=11040 Bytes=132480)

----------------------------------------------------------------------
Re: Usage of Query Rewrite in Materialized Views [message #92893 is a reply to message #92854] Wed, 23 January 2002 08:13 Go to previous messageGo to next message
Ajay Gandhi
Messages: 14
Registered: January 2002
Junior Member
You are not using the Month_number as a column in MV. Optimizer is going directly to the base tables. Try including the month_number in the MV and see the results.

Re: Usage of Query Rewrite in Materialized Views [message #93195 is a reply to message #92854] Tue, 19 August 2003 09:04 Go to previous message
Rich
Messages: 14
Registered: March 2002
Junior Member
Use this:

alter session set query_rewrite_integrity=trusted;

Rich
Previous Topic: Re: what are the objectives of datawarehousing as compared to other strategies
Next Topic: Datawarehouse w Warehouse builder-immediate req
Goto Forum:
  


Current Time: Thu Apr 02 01:27:43 CDT 2020