Home » RDBMS Server » Performance Tuning » Materialized view (oracle 11gr2)
Materialized view [message #623033] Wed, 03 September 2014 09:10 Go to next message
guddu_12
Messages: 211
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 #623034 is a reply to message #623033] Wed, 03 September 2014 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 13336
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd very much doubt it
Re: materialize view [message #623036 is a reply to message #623034] Wed, 03 September 2014 09:41 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Worth a test.
Re: materialize view [message #623037 is a reply to message #623036] Wed, 03 September 2014 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
>i want to know that if my query which is identical to materarialize view would hit it or not.
so produce EXPLAIN PLAN & see the answer for yourself
Re: materialize view [message #623042 is a reply to message #623033] Wed, 03 September 2014 10:36 Go to previous messageGo to next message
John Watson
Messages: 7664
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 Go to previous message
Kevin Meade
Messages: 2102
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

Previous Topic: dbms_addm.real_time_addm_report
Next Topic: Auto stats job window details
Goto Forum:
  


Current Time: Thu Nov 15 11:38:53 CST 2018