Menu
When I am creating an index on a view, it shows the following error:
ORA-01702: a view is not appropriate here
create view xx_emp for select * from emp;
What is the reason behind it?
308
Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.
Oracle SQL standards do not support creating indexes on views. If you need to index documents whose contents are in different tables, you can create a data storage preference using the USER_DATASTORE object.
It is not possible to create an index on a view. Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).
PostgreSQL UsagePostgreSQL doesn't support view indexes, but does provide similar functionality with Materialized Views. Queries associated with materialized views are run and the view data is populated when the REFRESH command is issued.
You cannot create an index over a view, which is just a query.
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.
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
