Can indexes be created on view in oracle

If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. All rights reserved by Burleson. Find centralized, trusted content and collaborate around the technologies you use most. Connect and share knowledge within a single location that is structured and easy to search.

You can , instead, create an index over a materialized view. A materialized view is a table which is created by evaluating a view, so that you can create an index over it. Keep in mind, however, that a materialized view is not updated for each modification of the base table s over which it is defined, so you should REFRESH it when it must be recalculated.

This will not work for complex views, but for your simple case the index will be in most cases used to access the data. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. All nonclustered indexes and auto-created statistics on the view are dropped when the clustered index is dropped. User-created statistics on the view are maintained.

Nonclustered indexes can be individually dropped. Dropping the clustered index on the view removes the stored result set, and the optimizer returns to processing the view like a standard view. Indexes on tables and views can be disabled. When a clustered index on a table is disabled, indexes on views associated with the table are also disabled. Indexes can only be created on views which have the same owner as the referenced table or tables. This is also called an intact ownership-chain between the view and the table s.

Typically, when table and view reside within the same schema, the same schema-owner applies to all objects within the schema. Therefore its possible to create a view and not be the owner of the view. On the other hand is also possible that individual objects within a schema have different explicit owners.

The following example creates a view and an index on that view. Two queries are included that use the indexed view in the AdventureWorks database. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services. Privacy policy. The actual amount of memory required depends on several details related to the rate of compaction and the representation of the types of data in the source or view.

We are working on a feature to let you see the size each index consumes. Creating an index may also force the first materialization of a view, which may cause Materialize to install a dataflow to determine and maintain the results of the view.