SQL 是否可以在视图列上创建索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31189274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to create index on view columns?
提问by guru
When I am creating an index on a view, it shows the following error:
当我在视图上创建索引时，它显示以下错误：
ORA-01702: a view is not appropriate here
ORA-01702: 此处不适合视图
create view xx_emp for select * from emp;
What is the reason behind it?
背后的原因是什么？
回答by Renzo
You cannot create an index over a view, which is just a query.
您不能在视图上创建索引，这只是一个查询。
You can, instead, create an index over a materializedview. 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 REFRESHit when it must be recalculated.
相反，您可以在物化视图上创建索引。物化视图是通过评估视图创建的表，因此您可以在其上创建索引。但是请记住，物化视图不会针对定义它的基表的每次修改而更新，因此REFRESH在必须重新计算时应该更新。
回答by Marmite Bomber
Define the index on the TABLE column (e.g. on EMP.EMP_ID)
在 TABLE 列上定义索引（例如在 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.
这不适用于复杂的视图，但对于您的简单情况，索引将（在大多数情况下）用于访问数据。
