-
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
-
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.
-
-
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.
-
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
-
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.
-
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|