this is my first time with MV and query rewrite and I have some troubles.
I have a fact table f and some dimension tables d1, d2, ..., dn.
The MV is the result of joining f, d1 and d2:
d1(city, state, region)
I created a oracle dimension for d1 but not for d2 because it's only one level.
The MV has these fields:
city, state, region, age, <numeric measures>
If a run queries like:
select state, age, <measures>
select region, age, <measures>
the rewriting works well and the MV is used instead of the original tables (the dimension on d1 is working).
But if I run
select state, <measures>
select city, <measures>
select age, <measures>
the rewriting doesn't work and MV isn't used.
I tried dbms_mview.explain_rewrite (<query>) and found this error:
QSM-01150: query did not rewrite
QSM-01284: materialized view MV has an anchor table d2 (for the first two queries) not found in query
I'm asking if this is normal or some strange behaviour.
Now, the rewrite works only if the tables joined in the query are the same of those in the MV.
Here is the code: ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SYSTEM SET QUERY_REWRITE_INTEGRITY = TRUSTED;
create materialized view sales.MV
refresh complete on demand
using trusted constraints enable query rewrite