Materialized view [message #623033] |
Wed, 03 September 2014 09:10 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Dear All,
I couldn't find answer any where so i am posting here.
I have a schema(test1) where i have some set of table and i have give grant select on these table to other schema. I have a materialized view in other schama(test2) based on (test1) tables. i want to know that if i run the query from schema test1 will it hit materialize view which is in schema test2.
In simple word my materialize view is in other schema and i want to know that if my query which is identical to materarialize view would hit it or not.
Thanks
[EDITED by LF: fixed topic title typo]
[Updated on: Sun, 07 September 2014 04:25] by Moderator Report message to a moderator
|
|
|
|
|
|
Re: materialize view [message #623042 is a reply to message #623033] |
Wed, 03 September 2014 10:36 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
orclz>
orclz> sho user
USER is "SCOTT"
orclz> create materialized view system.mv1 enable query rewrite as select sum(sal) from scott.emp;
Materialized view created.
orclz> set autot on exp
orclz> select sum(sal) from scott.emp;
SUM(SAL)
----------
29025
Execution Plan
----------------------------------------------------------
Plan hash value: 2958490228
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV1 | 82 | 1066 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
orclz>
|
|
|
Re: materialize view [message #623234 is a reply to message #623042] |
Sun, 07 September 2014 00:27 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Originally, Materialized View Rewrite was intended and still is intended to be TRANSPARENT. What this means is, you are not supposed to know that your query was redirected to a materialized view and your query is not required, indeed not expected to reference the materialized view. Given this intent, I would very much expect that direct grants to the MVIEW are not required. That means as long as you have rights to the original tables, you should be able to use the MVIEW no matter where it lives, or who created it.
However, I would make the following two notes:
1. what I have said above, I have not validated by looking at any manuals. You will have to scour them for the proper verification. Or you will have to create a test to show it.
2. if your MVIEW does not get used, do not assume that it is because you need privileges. Getting query rewrite to result in use of an MVIEW is often difficult because of the many details needed to make it happen. For example, I think the schema executing the query will need the query rewrite privilege? This too can be validated from the manuals. There was a time when I knew this specific piece of info but that was a while back. The MVEIW needs to have query rewrite enabled and depending upon its complexity other details set properly as well.
Again all the information is available in the manuals, along with documentation on how to determine why a specific MVIEW was not used for a specific query.
Have fun. Kevin
[Updated on: Sun, 07 September 2014 00:29] Report message to a moderator
|
|
|