Home » Server Options » Replication » Materialized view: Query rewrite with dimension rollup doesn't work (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0)
Materialized view: Query rewrite with dimension rollup doesn't work [message #297574] |
Fri, 01 February 2008 07:30 |
DenTimmer@hotmail.com
Messages: 4 Registered: June 2005
|
Junior Member |
|
|
Hi,
I'm trying to implement a materialized view. I have found an example on www.akadia.com/services/ora_dimensions.html where all scripts to set up the tables can be found. Query rewrite works fine as long as I run a query that groups on the same level as the materialized view (month in this case).
However, when I issue a query that calls for a higher level of aggregation (quarter level, just like in the example on the site) the optimizer refuses to rewrite the query.
I have created a dimension with all necessary levels (see script on site). Can anyone tell me what I am doing wrong?
Regards,
Denis
|
|
|
|
Re: Materialized view: Query rewrite with dimension rollup doesn't work [message #297600 is a reply to message #297574] |
Fri, 01 February 2008 12:22 |
DenTimmer@hotmail.com
Messages: 4 Registered: June 2005
|
Junior Member |
|
|
Here is the script to create the 2 tables and the data:
CREATE TABLE sales (
trans_date DATE,
cust_id INT,
sales_amount NUMBER
);
INSERT /*+ APPEND */ INTO sales
SELECT TRUNC(SYSDATE,'YYYY')+MOD(ROWNUM,366) trans_date,
MOD(ROWNUM,100) cust_id,
ABS(DBMS_RANDOM.RANDOM)/100 sales_amount
FROM all_objects
/
COMMIT;
BEGIN
FOR i IN 1 .. 4
LOOP
INSERT /*+ APPEND */ INTO sales
SELECT trans_date, cust_id,
ABS(DBMS_RANDOM.RANDOM)/100 sales_amount
FROM sales;
COMMIT;
END LOOP;
END;
/
CREATE TABLE time (
day PRIMARY KEY,
mmyyyy,
mon_yyyy,
qtr_yyyy,
yyyy
)
ORGANIZATION INDEX
AS
SELECT DISTINCT
trans_date DAY,
CAST (TO_CHAR(trans_date,'MMYYYY') AS NUMBER) MMYYYY,
TO_CHAR(trans_date,'MON-YYYY') MON_YYYY,
'Q' || CEIL(TO_CHAR(trans_date,'MM')/3) || ' FY'
|| TO_CHAR(trans_date,'YYYY') QTR_YYYY,
CAST(TO_CHAR(trans_date, 'YYYY') AS NUMBER) YYYY
FROM sales
/
After creating the tables, I analyze them, and set the session parameters:
ANALYZE TABLE SALES COMPUTE STATISTICS;
ANALYZE TABLE TIME COMPUTE STATISTICS;
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
Then I create the materialized view and define a dimension on the time table:
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT sales.cust_id,
SUM(sales.sales_amount) sales_amount,
time.mmyyyy
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY sales.cust_id, time.mmyyyy
/
CREATE DIMENSION time_dim
LEVEL DAY IS time.day
LEVEL MMYYYY IS time.mmyyyy
LEVEL QTR_YYYY IS time.qtr_yyyy
LEVEL YYYY IS time.yyyy
HIERARCHY TIME_ROLLUP
(
day CHILD OF
mmyyyy CHILD OF
qtr_yyyy CHILD OF
yyyy
)
ATTRIBUTE mmyyyy
DETERMINES mon_yyyy;
When I execute the following query, the query is rewritten and the materialized view is used:
SELECT time.mmyyyy, SUM(sales_amount)
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY time.mmyyyy
However, when I issue a query that calls for a higher level of aggregation the optimizer refuses to rewrite the query. Apparently, the dimension info is not used by the optimizer. Here is the query:
SELECT time.qtr_yyyy, SUM(sales_amount)
FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY time.qtr_yyyy
|
|
|
Re: Materialized view: Query rewrite with dimension rollup doesn't work [message #297615 is a reply to message #297574] |
Fri, 01 February 2008 13:25 |
DenTimmer@hotmail.com
Messages: 4 Registered: June 2005
|
Junior Member |
|
|
When using the DBMS_MVIEW.EXPLAIN_REWRITE procedure I get the following explanation:
QSM-01150: query did not rewrite
QSM-01082: Joining materialized view, SALES_MV, with table, TIME, not possible
QSM-01102: materialized view, SALES_MV, requires join back to table, TIME, on column, QTR_YYYY
|
|
|
Goto Forum:
Current Time: Sun Jun 30 22:22:51 CDT 2024
|