Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, KOMAL.

Asked: August 05, 2018 - 5:40 am UTC

Last updated: August 23, 2019 - 8:26 am UTC

Version: 12c

Viewed 50K+ times! This question is

You Asked

Hi Tom,

Thanks for your kind support always.

I have one question which is confusing me a lot. I know when any changes are made to base tables then those changes are reflected are on view as well when view is refreshed. But my question is reverse:

If an INDEX is created on a View then will it be created on base table also on it's own? or will it be created only on View?

Thanks in advance.

Regards,
Komal.

and Chris said...

You may be mixing-up regular views with materialized views.

You can't index a plain old view:

create table t (
  c1 int
);
create or replace view vw as 
  select * from t;
  
insert into t values ( 1 );

select * from vw;

C1   
   1 

create index i on vw ( c1 );

ORA-01702: a view is not appropriate here


All it does is store the text of the query. Not the data it returns. Querying a view is the same as running the query within it.

On the other hand, a materialized view does store the result of the query. This you can index. And have to refresh to see changes in the underlying tables:

create materialized view mv as 
  select * from t;
  
create index i on mv ( c1 );

insert into t values ( 2 );

commit;

select * from mv;

C1   
   1 

exec dbms_mview.refresh ( 'MV', 'C' );

select * from mv;

C1   
   1 
   2  


But the index only exists on the MV. Not the table:

select table_name, index_name 
from   user_indexes
where  table_name in ( 'T', 'MV' );

TABLE_NAME   INDEX_NAME   
MV           I    

Rating

  (2 ratings)

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

Comments

Apt response

Moinak Gangopadhyay, June 12, 2019 - 12:12 pm UTC

Great content. The examples made it simpler than it is.
Chris Saxon
June 12, 2019 - 1:09 pm UTC

Thanks!

Confuse at refreshDB

mtoha, August 23, 2019 - 7:14 am UTC

Hi, Sir
What is definition of C at last array 'MV' and 'C'?
exec dbms_mview.refresh ( 'MV', 'C' );
Chris Saxon
August 23, 2019 - 8:26 am UTC

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_MVIEW.html#GUID-DD332F65-A5BC-4DE6-814E-EAE2E0275F3D

A string of refresh methods indicating how to refresh the listed materialized views. An f indicates fast refresh, ? indicates force refresh, C or c indicates complete refresh, and A or a indicates always refresh. A and C are equivalent. P or p refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.