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