i'm working on 10gR2.
i have the following materialized view declaration
create materialized view new_mv
enable query rewrite
as
select id , name from a
union all
select b.id , h.name from b join h on (b.id=h.id);
i'm tring to get a similar query to be rewriten.
the REWRITE_CAPABILITY in all_mviews is set to "TEXTMATCH". i expected that when i'll execute the query as it apears in the "QUERY" coulmn of the same table it will be rewriten. it wont.
query_rewrite_enabled is set to TRUE for the session.
i tried forcing rewrite with a hint as folows :
select /*+ REWRITE(new_mv) */
from (
select id , name from a
union all
select b.id , h.name from b join h on (b.id=h.id));
it didnt work. i also tried
select /*+QB_NAME(x) REWRITE(@x new_mv) */
from (
select id , name from a
union all
select b.id , h.name from b join h on (b.id=h.id));
in this case the plan shows that the query block name is set but no rewrite accured.
i ran dbms_mview.explain_rewrite for the query and got these two messages :
QSM-01150: query did not rewrite
QSM-01263: query rewrite not possible when query references a dictionary table or view
i dont understand the second message. i dont reference any dictionary tables here.
i think the problem is the join. when i tried rewriting a similar query without a join it worked.
any ideas ?