视图只执行它们需要的连接，还是始终执行所有连接？
我使用的是 oracle DB。假设我有一个视图连接到三个表。每个视图都有两个字段。每个字段只需要三个表中两个表的数据。
如果我查询视图并仅返回一个字段，该视图是否仍连接到三个表，还是仅连接到计算该字段所需的两个表？
I am on an oracle DB. Lets say I have one view that joins to three tables. The view has two fields each. Each field only needs data from two of the three tables.
If I query the view and return only one field, does the view still join to three tables or just to the two tables that it needs to calculate the field?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
一般来说，它必须击中三张桌子。
考虑
“A”中的单个 ID 在 B 或 C 中可能有 0 个、1 个或多个匹配项。如果表“C”为空，则视图永远不会返回行，因此即使只查询 A.VAL 或B.VAL，它仍然需要查看“C”中是否有相应的行。
例外情况是，由于强制引用完整性约束，优化器知道“B”中的行始终在“A”中具有父行。在这种情况下，B.VAL 的选择不需要实际检查“A”中父行是否存在。这篇文章证明了这一点
Generally it will have to hit the three tables.
Consider
It is possible that a single ID in "A" to have zero, 1 or multiple matches in either B or C. If table "C" were empty, the view would never return a row, so even just querying A.VAL or B.VAL, it would still need to see if there was a corresponding row in "C".
The exception is when, because of an enforced referential integrity constraint, the optimizer knows that a row in 'B' will always have a parent row in 'A'. In that case, a select of B.VAL would not need to actually check the existence of the parent row in 'A'. This is demonstrated by this article
这可能取决于所使用的联接类型。如果都是内连接，肯定需要检查所有三个表。
That likely depends on the type of join being used. If they are all inner joins, it will definitely need to examine all three tables.
一般来说，数据库引擎会连接所有三个表以确保获得正确的答案。
Oracle 有时会删除其中一个不会改变结果的表。
如果满足以下条件，则可以完成此操作：-
要消除的表存在外键约束（即表中的一行）
可以保证找到）
该表未使用。即未从 where 子句等中选择。
In general, the database engine would join all three tables to ensure it got the right answer.
Oracle will sometimes eleminate one of the tables where this does not change the result.
This can be done if:-
There is a foreign key constraint to the table to be eleminated (i.e. a row in the table
can be guaranteed to be found)
The table is otherwise unused. i.e. not selected from, in the where clause, etc.