Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Query rewrite doesn't work on materialized view
Could you please confirm wether query rewrite should work in my situation?
The (simplified) datamodel contains the following objects:
My base fact table (large) :
Table: WTF_ORDERREGEL
orderdate_key
customer_key (used to join to WTD_CUSTOMER dimension)
amount
from this table, an aggregate (materialized view) was built, without orderdates, so much smaller:
Table: WTA_CUSTOMER_FACTS
customer_key (used to join to WTD_CUSTOMER dimension)
amount
A table that contains customer attributes
Table: WTD_CUSTOMER
customer_key
customer_name
The following query is executed:
Select a.customer_name /NAME IS NOT IN MATERIALIZED VIEW!/, sum(b.amount)
From wtd_customer a,
wtf_orderregel b
Where a.customer_key = b.customer_key
Group by customer_name
If I send the following query to Oracle, I expect Oracle to rewrite the query to use the materialized view, as all requested attributes from WTF_ORDERREGEL are available in the materialized view WTA_CUSTOMER_FACTS. It should then join the Customer dimension table.
So, the query to be executed by Oracle should become (after query rewrite):
Select customer_name, amount
From wtd_customer a,
wta_customer_facts b
Where a.customer_key = b.customer_key
Group by customer_name
Can I expect Oracle to do this kind of query rewrite?? Regards, Michiel Received on Wed Mar 20 2002 - 02:06:42 CST