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 Go to next message
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 #297575 is a reply to message #297574] Fri, 01 February 2008 07:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are your %rewrite% parameter values?
If you want us to know what you did wrong you have to post the whole session (as it is done in the article).
Before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: DBMS_refresh group
Next Topic: Index on a snapshot in 8i
Goto Forum:
  


Current Time: Sat Dec 10 22:45:07 CST 2016

Total time taken to generate the page: 0.10971 seconds