Can we create indexes on views?

Hi friends,

Recently one of the members of OTN forums has asked this question: Can we create indexes on views?

I told him absolutely not, because a view is nothing but a stored SQL query. It’s the window through which you can see the data of your choice. As the ultimate data container is the table, you impose all the required conditions(constraints, indexes etc…) on the table itself; they’ll be reflected in the view.

So it’s true that we can’t impose any constraints on views. OK, then what’s the case with the MVs (Materialized views)?? Can’t we create views on them too?

If you say we can’t, it’s wrong. We can create constraints, indexes on MVs as MVs contains the actual data i.e. the result of the query at the instant the query is executed.

create materialized view abc as select col1, col2 from t1;

ALTER MATERIALIZED VIEW abc add constraint 
abc_constraint check ( col2 < 10 ) deferrable;

So when you design applications, keep in mind that we can apply specific constraints, create indexes to attain the performance gains, on the selected set of data (of course through MVs).

There’s a caveat for this, which I’ve noticed while doing some time-pass testing 🙂

See that exercise here

SQL> create table t1 as select rownum rnm,'row_number:'||rownum rnmstr 
     from user_objects where rownum<=5;

Table created.  

SQL> alter table t1 add primary key(rnm);

Table altered.

SQL> create unique index t1_uk on t1(rnmstr);

Index created.

SQL> create materialized view mvt1 refresh fast with primary key 
     as select * from t1;

Materialized view created.

SQL> create unique index mvt1_uk on mvt1(rnmstr);

Index created.

SQL> select * from t1;

         R RNMSTR
---------- -------------------------------------------
         1 row_number:1
         2 row_number:2
         3 row_number:3
         4 row_number:4
         5 row_number:5

Now,we’ll interchange the positions of two of the rows

SQL> update t1 set RNMSTR='TEMP' where rnm=1;
1 row updated. 

SQL> update t1 set RNMSTR='row_number:1' where rnm=2; 

1 row updated. 

SQL> update t1 set RNMSTR='row_number:2' where rnm=1;

 1 row updated. 

SQL> commit; 

Commit complete. 

SQL> select * from t1;

         R RNMSTR
---------- -------------------------------------------
         1 row_number:2
         2 row_number:1
         3 row_number:3
         4 row_number:4
         5 row_number:5

Now when we try to refresh the mview we get the error.

SQL> exec dbms_mview.refresh('MVT1');
BEGIN dbms_mview.refresh('MVT1'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (YAS.MVT1_UK) violated
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

If we drop the unique index and recreate it as nonunique we can refresh the mview without errors.

SQL> drop index MVT1_UK;

Index dropped.

SQL> create index MVT1_AK on MVT1(rnmstr);

Index created.

SQL> exec dbms_mview.refresh('MVT1');

PL/SQL procedure successfully completed.

SQL> select * from MVT1;
         R RNMSTR
---------- -------------------------------------------
         1 row_number:2
         2 row_number:1
         3 row_number:3
         4 row_number:4
         5 row_number:5

The reason of this error is explained in Metalink note 284101.1. According to that note the refresh mechanism may temporarily leave the data in an inconsistent state during the refresh. The modifications are not made with the same order as the modifications to the base table. Because of this creating unique indexes, foreign keys or primary keys on mviews can cause problems in the refresh process. An mview is just a selective copy of the base table, so if we define the integrity constraints on the base table the mview will definitely comply to you expected constraints as the data in base table comply them. But you can create indexes without any fear if you are seeing a scope for performance gains.

Hope you liked it. I’m always welcome to suggestions/ comments

Regards,

CSM

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: