Oracle 视图限制
我正在将 sybase 存储过程转换为 oracle 视图。这是他们想要的，这不是我的第一选择。我的问题是： oracle 视图有任何限制吗？总列数？可以为视图创建索引吗？由于我将创建子视图并在视图中加入视图，因此您可以执行的视图层数是否有限制？ 谢谢
I am converting sybase stored procedures into oracle views. This is what they want and this is not my first choice. My question is:
Are there any limitations in oracle views? Total number of columns? Can you create indexes for views? Since I will be creating subviews and joining views within views, are there any limitations on how many layers of views you can do?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
全部内容均包含在此处：http://docs.oracle.com/database/121/REFRN/GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76.htm#REFRN0043
视图中的列数有与表中的列数相同的限制。
不，你不能。但是，您可以创建一个可以索引的物化视图
All covered here: http://docs.oracle.com/database/121/REFRN/GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76.htm#REFRN0043
The number of columns in a view has the same limits as the number of columns in a table.
No you cannot. However you cate create a materialized view, that can be indexed
表中的列数限制为 1000 列。如果视图也有类似的限制，我不会感到惊讶。但是，如果您要创建一个包含 1000 列的视图，那么您可能做错了一些事情。
您无法在视图上创建索引，但可以在针对视图的查询可以使用的基础表上创建索引。您可以对物化视图建立索引，因为顾名思义，它们将数据物化为单独的结构。但是，您必须在提交时刷新物化视图，这会增加事务的开销，或者容忍陈旧的数据并按某个计划刷新物化视图。
您可以拥有的视图层数没有限制。根据 Oracle 版本、视图的复杂性以及约束的存在等因素，您最终的查询可能会迫使 Oracle 执行额外的工作（即在视图层中加入最终查询不会执行的其他表）实际需要）或者对于优化器来说太复杂而无法找到合适的计划。
You're limited to 1000 columns in a table. It wouldn't shock me if there was a similar limit for views. But if you're creating a view with 1000 columns, you're probably doing something very wrong.
You cannot create indexes on views but you can create indexes on the underlying tables that queries against views can use. You can index materialized views since, as the name implies, they materialize the data into a separate structure. But then you have to deal with refreshing the materialized view on commit, which adds overhead to transactions, or tolerate stale data and refresh the materialized view on some schedule.
There is no limit to the number of layers of views you can have. Depending on the Oracle version, the complexity of the views, and things like the presence of constraints, you can end up with queries that either force Oracle to do extra work (i.e. joining in additional tables in view layers that your end query doesn't actually need) or that are too complex for the optimizer to find a decent plan.
视图的复杂性没有任何重大限制。
Oracle 基于成本的优化器在弄清楚如何使用基础表的索引对视图执行查询方面做得不错（虽然难以理解）。
如果该性能还不够，您可以考虑研究物化视图。
执行您正在执行的此类项目的通常方法是让视图正常工作，然后使用 EXPLAIN PLAN 进行优化。
There aren't any significant limitations on the complexity of views.
The Oracle cost-based optimizer does a decent (if inscrutable) job of figuring out how to carry out queries to views using the indexes of the underlying tables.
If that performance isn't enough, you might consider looking into materialized views.
The usual way of doing the kind of project you're doing is to get the views working, then use EXPLAIN PLAN to do the optimization.