Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Piyush.

Asked: June 07, 2011 - 12:57 am UTC

Last updated: June 09, 2011 - 9:59 am UTC

Version: 10G

Viewed 10K+ times! This question is

You Asked

Consider the situation below,

create table baseTab( key integer primary key, val varchar2(10));

insert into baseTab values( 1, 'a' );
insert into baseTab values( 2, 'b' );
insert into baseTab values( 3, 'c' );
insert into baseTab values( 4, '');



Now creating the Materialized View

create materialized view MyMV
  REFRESH COMPLETE 
  as select * from BaseTab
;



Now, when I am creating this 'MyMV' view, it is internally creating a storage for the data of the SELECT query. The storage is created as a table with the name of 'MyMV'. We can see this object in sys.obj$ e.g.

SQL> select obj#, Name, type#  from sys.obj$ where name like 'MYMV';      

      OBJ# NAME           TYPE#
---------- ------------------------------ ----------
     34262 MYMV        2
     34264 MYMV       42



The result is showing two records, of course one is for MATERIALIED VIEW & other is TABLE. My question is, how oracle is allowing the same object name for two objects ? As per my understanding, Tables & Materialized Views are stored in the same namespace, hence it should now allow to create the same named object in the same namespace.
- Is their any exception for Materialized View ?
- In case of MVs, the Table object is created internally & hence it is allowed ?

Can you share your views on this please ?


Thanks,
-Piyush

and Tom said...

Tables and materialized views are not stored in the same namespace.

A materialized view will either create a table named after the materialized view OR it will use a prebuilt table of the same name. That means that if you have a table T that already exists and you attempt to create a materialized view T - it will either

a) fail - because the materialized view will not be able to create the table
b) succeed - because you used the prebuilt table option and the materialized view will use that existing table.

ops$tkyte%ORA11GR2> create table t1 as select * from dual;

Table created.

ops$tkyte%ORA11GR2> create table t2 as select * from dual;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create materialized view t1 as select * from dual;
create materialized view t1 as select * from dual
                                             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


ops$tkyte%ORA11GR2> create materialized view t2 on prebuilt table as select * from dual;

Materialized view created.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What will happen on Select

Ramakrishnan, June 08, 2011 - 4:28 pm UTC

Hi Tom,
Sorry, how will you access these tables separately... If i put select * from t1 it will access the materialized view or table or throw an run time error.
Tom Kyte
June 08, 2011 - 5:15 pm UTC

it always accesses the table.


there is nothing to be 'separated' here. A materialized view always has a table underneath it.

select * from table is what always happens.

A materialized view is more metadata on top of a real table. It contains a defining query, refresh periods, refresh types, the ability to be used in a rewrite - or not, and so on.


Piyush, June 09, 2011 - 2:01 am UTC

oh ok..

I didn't observed the NAMESPACE Column in the sys.obj$.

Here is the output ->

 select obj#, Name, type#,NAMESPACE from sys.obj$ where name like 'MYMV';

      OBJ# NAME           TYPE#  NAMESPACE
---------- ------------------------------ ---------- ----------
     34262 MYMV        2       1
     34264 MYMV       42      19




However, when we refer 'MYMV', it always point to the Materialized View & not table. e.g. when I am trying to insert something to 'MYMV' (assuming it table), it is throwing error "ORA-01732: data manipulation operation not legal on this view".
Apart from this, their must be some way to refer/use 'MYMV' table instead of view ? I can understand MV needs a storage container, so it is creating a table under it, but why it is not hidden?
However, where can I see the Namespace name & its ID stored in the Database ?
Tom Kyte
June 09, 2011 - 9:59 am UTC

stop querying the sys tables, it is not useful.

The materialized view, as I said, is a metadata layer (data about data). You cannot insert into it because there exists a bit of metadata that says "you cannot do that".


You are *using* the table.

The materialized view contains metadata that describes how you may use this table.

It is not hidden because you need to do things like create indexes on it, maintain it, manage it.


The namespace is implied - by the object type, just look at the object type.

Piyush, June 10, 2011 - 4:54 am UTC

Coool. Thanks TOM for your valuable suggestion.

Thi gives me enough hint internally. Basically the metadata itself instruct server to redirect user whenever it is trying to refer TABLE.

Here is something I got from web related to Namespace.

This is decode of namespace from definition of gv$library_cache_memory:

decode(kglsim_namespace,
0,'SQL AREA',
1,'TABLE/PROCEDURE',
2,'BODY',
3,'TRIGGER',
4,'INDEX',
5,'CLUSTER',
6,'OBJECT',
7,'PIPE',
13,'JAVA SOURCE',
14,'JAVA RESOURCE',
32,'JAVA DATA', '?'),

1 - TABLE/PROCEDURE.


In case of MVs, the namespace column in SYS.OBJ$ is becomes 19. Anyway, I will not go through the SYSTEM TABLEs now (as you suggested).

Thanks for all your suggestions. Will hit here again if needed.

Thanks,
-Piyush

materialized view and table

venkim, May 01, 2013 - 5:17 pm UTC


Tom,
Very good explanation about the 01732 and the table name and materailized view on prebuilt table. I believe there is no change on 11gR2 either.

Thanks

Bhanu Prasad, July 11, 2013 - 5:04 pm UTC

It helps me a lot to understand the materialized view. Thank you very much.