Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: Materialized view and query rewrite

    Hello,
    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)
    d2(age)

    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
    build immediate
    refresh complete on demand
    using trusted constraints enable query rewrite
    as <query>


    Any help would be really appreciated.

  2. #2
    Join Date
    Feb 2009
    Posts
    2

    Solved

    I've been answered on another forum, what happens is absolutely normal, it's the way it has to be.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •