Oracle 视图与 Oracle 中的连接表 [英] Oracle View vs joining tables in Oracle

问题描述

我知道视图是 Oracle 中底层表或表集的窗口.例如，如果我有一个通过连接多个表创建的视图，当我从视图中选择数据时，视图会执行实际的连接操作吗?视图的性能是否比连接多个表来获取数据更好?在性能方面是否相同?

I understand that a view is a window to an underlying table or set of tables in Oracle. For instance if I have a view that is created by joining multiple tables , will the view perform the actual join operations when I select data from the view? Does a view perform better than joining multiple tables to fetch data or is it the same with respect to performance?

推荐答案

单个查询和使用视图的逻辑等效查询之间通常没有性能差异.

There is usually no performance difference between a single query and a logically equivalent query that uses views.

Oracle 具有优化器转换，可以将视图与外部查询结合起来；谓词推送、简单和复杂的视图合并等.将视图视为构建大型查询的文本宏，而不是返回行的函数.

Oracle has optimizer transformations that can combine views with the outer query; predicate pushing, simple and complex view merging, etc. Think of views more like a text macro that builds a large query, instead of a function that returns rows.

例如，在下面的查询中，Oracle 可能足够聪明，可以将主键列上的谓词推送到视图中.尽管视图本身可能会返回数百万行，但是当整个查询运行时，Oracle 将首先在主键列上应用谓词.

For example, in the below query Oracle would probably be smart enough to push the predicate on the primary key column into the view. Although the view by itself might return millions of rows, when the entire query is run Oracle will apply the predicate on the primary key column first.

select *
from view_returns_millions_of_rows
where primary_key_value = 1;

这篇关于Oracle 视图与 Oracle 中的连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！