Can we create indexes on views?

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.

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.