16 March, 2009

Materialized Views and Tables

Responding to a recent forums posting on a Materialized View having the same name as a Table, here I demonstrate the case.

In this example below, MV_1 is created as a Materialized View. Oracle automatically creates a table with the same name as the underlying table. This is for the storage of the data that the Materialized View, by definition, is supposed to be physically present .
When I later drop MV_1, the underlying table also gets dropped.

In the second case, MV_2 is actually a table. With the table pre-existing, I create a Materialized View on the Prebuilt Table. If I drop the Materialized View, table remains -- because it predates the MV definition.



SQL> connect test_user/test_user
Connected.
SQL> create table source_tb_1 (col_1 varchar2(5));

Table created.

SQL> create materialized view mv_1 as select * from source_tb_1;

Materialized view created.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
MV_1 TABLE
MV_1 MATERIALIZED VIEW
SOURCE_TB_1 TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SOURCE_TB_1
MV_1

SQL> drop materialized view mv_1;

Materialized view dropped.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SOURCE_TB_1 TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SOURCE_TB_1

SQL> create table mv_2 (col_1 varchar2(5));

Table created.

SQL> create materialized view mv_2 on prebuilt table as select * from source_tb_1;

Materialized view created.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
MV_2 TABLE
MV_2 MATERIALIZED VIEW
SOURCE_TB_1 TABLE

SQL> select table_name from user_tables ;

TABLE_NAME
------------------------------
SOURCE_TB_1
MV_2

SQL> drop materialized view mv_2;

Materialized view dropped.

SQL> select object_name, object_type from user_objects order by 1;

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
MV_2 TABLE
SOURCE_TB_1 TABLE

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SOURCE_TB_1
MV_2

SQL>



Thus, "MV_1" appears as both a Table and a Materialized View. Dropping the Materialized View results in the table being dropped. Conversely, "MV_2" is actually a Table with a Materialized View being defined over it. Dropping the Materialized View only drops the MV definition, the table remains.
.
.

7 comments:

Anonymous said...

if i had already some data in table mv_2 and i create mv_2 materialized view on it, so oracle will overwrite my existing table data with mv_2 materialized view table data?

Regards!

Hemant K Chitale said...

If you have existing data in the table, the MV definition uses that data.

Also see http://hemantoracledba.blogspot.com/2009/03/materialized-view-on-prebuilt-table.html

Anonymous said...

Thanks Hemant , U saved me a lot of time ..

ron said...

thanks hemant..that helped

Anonymous said...

Thank Hemant very much!
it's very easy to understand!

Anonymous said...

HI Hemant,

I have one question :
My GSS is failing with the error
Error:-
#1: ERROR: While GATHER_TABLE_STATS:
object_name=APPS.GL_ACCT_WISE_DATE_WISE_SUM_MV***ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired***

The table which is being refered is MVIEW table and there is always lock on this table

Could you please help me on the same.

Thanks in advance

Hemant K Chitale said...

Anonymous,
I don't see why a Gather_%_Stats would raise an ORA-54 against an MV table. An ORA-54 error occurs generally when you attempt DDL against an object with active DML.

An MV table is "locked" (in the sense it prevents DDL) when it is being updated by a Refresh.

Hemant