Is it possible to create index on view columns?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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/