oracle 嵌套数据库视图可以吗？

Is it OK to nest database views?

提问by Galghamon

In the world of oracle, I have the impression that views based on other views are considered to be bad practice. I myself have complained about this when the trying to solve performance issues and the nesting seemed excessive and hid away unneeded complexity in the underlying views. Now I find myself in the situation of thinking that it may not be so clear-cut:

在 Oracle 世界中，我的印象是基于其他视图的视图被认为是不好的做法。当试图解决性能问题和嵌套似乎过度并在底层视图中隐藏了不需要的复杂性时，我自己也抱怨过这一点。现在我发现自己处于一种认为它可能不那么明确的情况：

I have users who very specifically need the accounting numbers from one view to match those of another that does further processing on them. If they ever change anything in one, they want the other to reflect that immediately, without anyone having to think of this requirement in a few years time and reports showing non-matching numbers while they figure things out.

我有用户非常特别需要一个视图中的会计编号来匹配另一个对其进行进一步处理的会计编号。如果他们对其中的任何内容进行了更改，他们希望另一个立即反映出来，而无需任何人在几年后考虑此要求，并且在他们解决问题时报告显示不匹配的数字。

Is it ok to nest views in this case?

在这种情况下可以嵌套视图吗？

Does it change things if the inner view contains a further, important view that contains relevant prices (i.e. you're "always" supposed to use this view when determining prices)?

如果内部视图包含包含相关价格的更进一步的重要视图（即您“总是”应该在确定价格时使用此视图），它是否会改变事情？

采纳答案by ConcernedOfTunbridgeWells

The main problem with nesting views is that the query optimiser is more likely to get confused and produce a sub-optimal plan. Apart from this, there is no specific overhead to using views on views unless they do something that the optimiser can't push predicates down into.

嵌套视图的主要问题是查询优化器更容易混淆并产生次优计划。除此之外，在视图上使用视图没有特定的开销，除非它们做了一些优化器无法将谓词下推的事情。

This means that the best option is to try the nested views. See if you get sensible query plans out of the reports. If it does cause problems then you may have to re-think your strategy.

这意味着最好的选择是尝试嵌套视图。看看您是否从报告中获得了合理的查询计划。如果它确实引起了问题，那么您可能必须重新考虑您的策略。

回答by David

I'll just answer from a best practices perspective:

我只是从最佳实践的角度回答：

There are only a few times I would pause about using Views on Views.

只有几次我会暂停使用 Views on Views。

1. Nesting seems to be getting out of hand ... like over 3 levels deep. The reason I am nesting is to make the code easier to maintain. As soon as I start getting to this point it starts feeling a little too complicated to understand.

2. Nesting a view which uses analytical functions. I've personally, for one reason or another, not had very good experience with nesting views with analytical function.

3. Nesting views that do full scans by nature. While I think the the query optimizer is probably smart enough to handle this it just looks wrong to me when I'm reviewing the logic of the view.

4. Performance is a great concern. This is not to say the optimizer might get it wrong but this is to say before I release it, I'm going to test it to see if I can't figure a faster way to do it.

1. 嵌套似乎失控了......就像深度超过 3 级。我嵌套的原因是为了使代码更易于维护。一旦我开始谈到这一点，它开始感觉有点太复杂而无法理解。

2. 嵌套使用分析函数的视图。出于某种原因，我个人在嵌套具有分析功能的视图方面没有很好的经验。

3. 本质上进行完整扫描的嵌套视图。虽然我认为查询优化器可能足够聪明来处理这个问题，但当我查看视图的逻辑时，它看起来是错误的。

4. 性能是一个很大的问题。这并不是说优化器可能会出错，而是说在我发布它之前，我将对其进行测试，看看我是否想不出更快的方法来做到这一点。

Other than that I've used views on views quite successfully.

除此之外，我已经非常成功地使用了视图的视图。

回答by HLGEM

I think you are on the slippery slope here where code reuse and performance are going to clash. You can try it and see how badly it wil affect performance. We have a couple of databases here where they have stacked views on top of views and frankly the performance is miserable and now everyone involved wished thhat they had not designed that way.

我认为您在这里处于滑坡上，代码重用和性能将发生冲突。您可以尝试一下，看看它对性能的影响有多严重。我们这里有几个数据库，它们在视图之上堆叠了视图，坦率地说，性能很糟糕，现在所有相关人员都希望他们没有那样设计。

回答by Rob

There's always a tradeoff between coding time, ease or quality of code, and performance.

在编码时间、代码的易用性或质量以及性能之间总是需要权衡。

Nesting views is really easy to code and, given the right circumstances, makes it easy to read. It can also reduce time. It's arguably reducing the quality and often reduces performance... but by how much?

嵌套视图真的很容易编码，并且在适当的情况下，使其易于阅读。它还可以减少时间。可以说它会降低质量并且经常会降低性能……但是降低了多少？

It's all subjective. If it makes sense, roll with it. Don't prematurely optimize your code.

这都是主观的。如果它有道理，那就滚吧。不要过早地优化您的代码。

回答by BacMan

I'm nesting views 3 levels deep in Oracle 10g R2. Performance seem corelate to the select statements in the views, rather than the view depth. In particular the "IN" clause seems to be causing a lot of trouble.

我在 Oracle 10g R2 中嵌套了 3 个级别的视图。性能似乎与视图中的选择语句相关，而不是与视图深度相关。特别是“IN”子句似乎引起了很多麻烦。

回答by lod3n

Best practice doesn't always cover everything. I think you have a clear-cut justification for nesting them, just this once.

最佳实践并不总是涵盖所有内容。我认为你有一个明确的理由来嵌套它们，就这一次。

回答by C Bauer

It's also a good thing to note in the process of building complicated database queries sometimes nested views are the best thing - for one example, if you need any math operator built on 2 columns, for instance SUM(Col1, Col2) it can be better to nest views so that the sum is a column in itself instead of having to do something like

在构建复杂的数据库查询的过程中注意有时嵌套视图是最好的也是一件好事 - 例如，如果您需要任何构建在 2 列上的数学运算符，例如 SUM(Col1, Col2) 它可能会更好嵌套视图，以便总和本身就是一列，而不必执行类似的操作

"SELECT Total / SUM(Col1, Col2), SUM(Col1, Col2) * 2, Col1 / SUM(Col1, Col2) ..."

“选择总计 / SUM(Col1, Col2), SUM(Col1, Col2) * 2, Col1 / SUM(Col1, Col2) ...”

However I'm not sure I understand 100% - Why are there 2 views needed? Can't both users look at the 1 view and further processing be derived in a view another layer above that one?

但是我不确定我是否理解 100% - 为什么需要 2 个视图？不能两个用户都查看 1 视图并在该视图之上的另一层视图中导出进一步处理吗？

回答by JeffO

The best reasons to use a view would be to:

使用视图的最佳理由是：

1. prevent duplicating the same query.
2. prevent direct access to tables by other query writers
3. create a layer of security (similar to #2).

1. 防止重复相同的查询。
2. 防止其他查询编写器直接访问表
3. 创建一个安全层（类似于#2）。

I do realize it can also help to simplify a more complex query, but you get use to it. You may find that a user defined function (table) may be a better solution. Either way, performance will take a hit.

我确实意识到它也有助于简化更复杂的查询，但您已经习惯了。您可能会发现用户定义的函数（表）可能是更好的解决方案。无论哪种方式，性能都会受到影响。

回答by Jonathan Leffler

Nested views can make sense. Just be careful that you don't make them too general.

嵌套视图是有意义的。请注意不要让它们过于笼统。

---

---

I did see a system that had a view with 14 tables mentioned explicitly, some of them connected with outer self-joins, and some of the 'tables' were themselves views. I didn't like it much, but the DBMS coped with it astonishingly well (given that it was back in the late 80s). A lot of the schema was machine generated by a data modelling tool.

我确实看到了一个系统，它有一个明确提到的 14 个表的视图，其中一些与外部自联接有关，而一些“表”本身就是视图。我不太喜欢它，但 DBMS 处理得非常好（考虑到它是在 80 年代后期）。许多模式是由数据建模工具机器生成的。

CREATE VIEW IBB_V_Project AS
    SELECT  A.Project_Iref,
            A.Section_Iref,
            B.Section_Eref,
            N.Company_Iref,
            N.Company_Name,
            A.Product_Desc,
            A.Project_Type_Iref,
            D.Project_Type,
            A.Person_Iref,
            F.Full_Name,
            A.Respon_Iref,
            G.Post_Location,
            A.Project_Stat_Iref,
            E.Project_Status,
            A.Source_Iref,
            I.Source,
            A.Sic_Iref,
            L.Sic_Eref,
            A.Op_Activity_Iref,
            M.Op_Activity_Desc,
            A.Involve_Iref,
            K.IBB_Involvement,
            A.Nature_Iref,
            C.Nature_Of_Next_Act,
            A.Internat_Mobile,
            A.Whether_Cop_Case,
            A.Closed_Ind,
            A.Next_Action_Date,
            A.Creation_Date,
            A.Last_Edit_Date,
            A.Last_Editor_Iref,
            H.Logname

    FROM    IBB_Project A,
            IBB_Section B,
            IBB_R_Proj_Type D,
            IBB_R_Project_Stat E,
            IBB_Personnel H,
            OUTER IBB_R_Next_Act C,
            OUTER IBB_Personnel F,
            OUTER (IBB_Post_Respon X, OUTER IBB_V_Post_Resp2 G),
            OUTER IBB_R_Source I,
            OUTER IBB_R_Involvement K,
            OUTER IBB_Sic L,
            OUTER IBB_Op_Act M,
            OUTER IBB_V_Proj_Co2 N

    WHERE   A.Section_Iref      = B.Section_Iref
      AND   A.Project_Type_Iref = D.Project_Type_Iref
      AND   A.Project_Stat_Iref = E.Project_Stat_Iref
      AND   A.Last_Editor_Iref  = H.Person_Iref
      AND   A.Nature_Iref       = C.Nature_Iref
      AND   A.Person_Iref       = F.Person_Iref
      AND   A.Respon_Iref       = X.Respon_Iref
      AND   X.Respon_Iref       = G.Person_Iref
      AND   A.Source_Iref       = I.Source_Iref
      AND   A.Sic_Iref          = L.Sic_Iref
      AND   A.Op_Activity_Iref  = M.Op_Activity_Iref
      AND   A.Project_Iref      = N.Project_Iref
      AND   A.Involve_Iref      = K.Involve_Iref;

The outer join notation is specific to Informix (which now supports the SQL standard notation too).

外连接表示法特定于 Informix（它现在也支持 SQL 标准表示法）。

Note that IBB_V_Post_Resp2 and IBB_V_Proj_Co2 are both themselves views. In fact, IBB_V_Proj_Co2 was a 3-table view, exact details unknown but of the form:

请注意，IBB_V_Post_Resp2 和 IBB_V_Proj_Co2 本身都是视图。事实上，IBB_V_Proj_Co2 是一个 3 表视图，确切的细节未知，但形式如下：

CREATE VIEW IBB_V_Proj_Co2 AS
    SELECT  A.Project_Iref,
            A.Some_Other_Col col01,
            B.Xxxx_Iref,
            B.Some_Other_Col col02,
            C.Yyyy_Iref,
            C.Some_Other_Col col03
    FROM    IBB_Project A,
            OUTER (IBB_R_Xxxx B, IBB_R_Yyyy C)
    WHERE   A.Xxxx_Iref = B.Xxxx_IrEf
      AND   B.Yyyy_Iref = C.Yyyy_Iref;

This means that the IBB_V_Project view has an outer self-join on IBB_Project. The IBB_V_Post_Resp2 view probably involved 3 tables too (my notes on that were a bit unclear, way back in 1993, when I recorded this information).

这意味着 IBB_V_Project 视图在 IBB_Project 上有一个外部自联接。IBB_V_Post_Resp2 视图可能也涉及 3 个表（我的笔记有点不清楚，早在 1993 年，当我记录此信息时）。

CREATE VIEW IBB_V_Post_Resp2 AS
    SELECT  A.Person_Iref,
            A.Some_Other_Col col01,
            B.Xxxx_Iref,
            B.Some_Other_Col col02,
            C.Yyyy_Iref,
            C.Some_Other_Col col03
    FROM    IBB_Personnel A,
            IBB_R_Xxxx B,
            IBB_R_Yyyy C
    WHERE   A.Xxxx_Iref = B.Xxxx_Iref
      AND   B.Yyyy_Iref = C.Yyyy_Iref;

The Zzzz_Iref columns were either SERIAL or INTEGER foreign keys referencing a SERIAL key.

Zzzz_Iref 列是引用 SERIAL 键的 SERIAL 或 INTEGER 外键。

The primary view definition refers to 14 tables, with 4 inner joins and 9 outer joins. When the cross-referenced views are taken into account, there are 18 tables in total, with 7 inner joins and 10 outer joins.

主视图定义引用 14 个表，具有 4 个内部联接和 9 个外部联接。当考虑到交叉引用的视图时，总共有 18 个表，其中有 7 个内连接和 10 个外连接。

回答by Jonathan Leffler

don't really want to get caught up in the whole nested view thing

真的不想陷入整个嵌套视图的事情

have a think about this for an idea...your trying to join onto a table to find mismatches...i would use the Oracle function 'minus'....MINUS selects elements from the first table and then removes rows that are also returned by the second SELECT statement.

想一想这个想法......你试图加入一个表来查找不匹配......我会使用Oracle函数'减'......MINUS从第一个表中选择元素，然后删除那些行也由第二个 SELECT 语句返回。

SELECT num FROM (SELECT 1 AS num FROM DUAL UNION ALL SELECT 2 AS num FROM DUAL UNION ALL SELECT 3 AS num FROM DUAL) base_view

SELECT num FROM (SELECT 1 AS num FROM DUAL UNION ALL SELECT 2 AS num FROM DUAL UNION ALL SELECT 3 AS num FROM DUAL) base_view

MINUS

减

SELECT 2 AS num FROM DUAL

SELECT 2 AS num from DUAL