CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
guru
guru

Reputation: 153

Is it possible to create index on view columns?

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?

Upvotes: 13

Views: 80052

Answers (3)

Ramazan Sağır
Ramazan Sağır

Reputation: 5173

You can use INDEXED VIEW an other keyword SHEMABINDING on MSSQL. for more: https://dbtut.com/index.php/2019/03/24/what-is-indexed-view-in-sql-server-and-how-to-create-an-indexed-view/

Upvotes: -1

Renzo
Renzo

Reputation: 27424

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.

Upvotes: 17

Marmite Bomber
Marmite Bomber

Reputation: 21073

Define the index on the TABLE column (e.g. on EMP.EMP_ID)

create index emp_idx on emp (emp_id);

and use it while querying the view

select * from xx_emp where emp_id = 1;

This will not work for complex views, but for your simple case the index will be (in most cases) used to access the data.

Upvotes: 5

Related Questions