Reg:materialized view query rewrite
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Reg:materialized view query rewrite

  1. #1
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339

    Reg:materialized view query rewrite

    hi
    when i taken the explain plan of the MV,the output is like this

    Elapsed: 00:00:00.04
    SQL> select operation,object_name from plan_table;

    OPERATION OBJECT_NAME
    ------------------------------ --------------------
    SELECT STATEMENT
    MAT_VIEW REWRITE ACCESS MV_BIGTAB

    means the query rewite is happening
    ........

    but when i checked the same thing using the dbms_mview.explain_rewrite
    the output i got is..

    SQL> select message from rewrite_table order by sequence;

    MESSAGE
    --------------------------------------------------------------------------------
    QSM-01150: query did not rewrite
    QSM-01263: query rewrite not possible when query references a dictionary table o
    r view

    it is saying the query did not rewirte...

    i am new to MV and the query rewrite....
    why it is showing like this

    gopu

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Are you quering dictionary tables or views?... meaning, does your MV query selects from dictionary tables or views?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    From views

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Code:
    oerr QSM 1263
    01263, 00000, "query rewrite not possible when query references a dictionary table or view"
    // *Cause:    Query rewrite is not allowed if query references any dictionary
    //            tables/views.
    // *Action:   none
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    That means query rewrite is not happening ?

    but here it shows that it is happening...why it is showing like that ?
    SQL> select operation,object_name from plan_table;

    OPERATION OBJECT_NAME
    ------------------------------ --------------------
    SELECT STATEMENT
    MAT_VIEW REWRITE ACCESS MV_BIGTAB

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    don't ask me, ask Oracle by tracing it, trace would show the truth
    Last edited by PAVB; 04-02-2008 at 06:34 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    thanks

  8. #8
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    thanks pavb for the valuable answers....

    i got the answer....



    MESSAGE
    --------------------------------------------------
    QSM-01151: query was rewritten
    QSM-01209: query rewritten with materialized view,
    MV_BIGTAB, using text match algorithm
    Last edited by gopu_g; 04-02-2008 at 07:41 AM.

  9. #9
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    339
    thanks pavb for the valuable answers....

    i got the answer....

    SQL> select message from rewrite_table;

    MESSAGE
    --------------------------------------------------------------------------------
    QSM-01151: query was rewritten
    QSM-01209: query rewritten with materialized view, MV_BIGTAB, using text match a
    lgorithm

Posting Permissions

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



Click Here to Expand Forum to Full Width