oracle 使用 VIEW 进行 SELECT 操作可以提高性能吗？

Can using a VIEW for SELECT operations improve performance?

提问by Maxim Veksler

Working on improving performance of our decision center, one of the bottlenecks we identify is the DB.

致力于提高决策中心的性能，我们发现的瓶颈之一是数据库。

So I wonder, does oracle compiles an Execution Plan for it's views?

所以我想知道，oracle 是否为它的视图编译了一个执行计划？

Lets hypothetically assume I have a defined query being used 10000times during a request. The query looks something like :

让我们假设我有一个已定义的查询10000在请求期间使用次数。查询类似于：

select A, B, C 
from aTbl, bTbl left join cTbl on bTbl.cTblID = cTbl.objectkey
where aTbl.objectkey = bTbl.parentkey

In the code I would like to fetch the result of the query above with additional filtering parameter, for example: WHERE aTbl.flag1 = <<NUMBER>>

在代码中，我想使用附加过滤参数获取上面查询的结果，例如： WHERE aTbl.flag1 = <<NUMBER>>

Now I have 2 options:

现在我有两个选择：

1. Creating a prepared statement using the above SQL, then reusing the object.
2. Putting the above select (aTbl, bTbl, cTbl)into a VIEW, then creating a prepared statement on this view, thus benefiting the from execution plan precompiled b Oracle.

1. 使用上述创建准备好的语句SQL，然后重用该对象。
2. 将上述内容select (aTbl, bTbl, cTbl)放入 a 中VIEW，然后在此视图上创建一个准备好的语句，从而受益于预编译 b Oracle 的执行计划。

What would you suggest?

你有什么建议？

回答by Quassnoi

Oraclemaypush predicate into a view if it thinks it will improve the plan.

Oracle如果它认为它会改进计划，则可以将谓词推入视图。

If you want to avoid this, you may use either of the following:

如果您想避免这种情况，您可以使用以下任一方法：

1. Add a /*+ NO_MERGE */or /*+ NO_PUSH_PRED */hint into the view definition
2. Add a /*+ NO_MERGE (view) */or /*+ NO_PUSH_PRED (view) */hint into the query that uses the view.

1. 在视图定义中添加/*+ NO_MERGE */或/*+ NO_PUSH_PRED */提示
2. 在使用视图的查询中添加/*+ NO_MERGE (view) */或/*+ NO_PUSH_PRED (view) */提示。

If you want to force this, use their couterparts /*+ PUSH_PRED */and /*+ MERGE */

如果您想强制执行此操作，请使用他们的 couterparts/*+ PUSH_PRED */和/*+ MERGE */

As long as performance is concerned, there is no difference between using a defined view (if it's not a MATERIALIZED VIEWof course) or an inline view (i. e. subquery).

就性能而言，使用定义的视图（如果它不是MATERIALIZED VIEW当然）或内联视图（即子查询）之间没有区别。

Oraclecompiles plans not for views, but for exact SQLtexts.

Oracle编制计划不是为了观点，而是为了准确的SQL文本。

That is, for the following statements:

也就是说，对于以下语句：

SELECT A, B, C 
FROM   aTbl, bTbl
LEFT JOIN cTbl ON 
  bTbl.cTblID = cTbl.objectkey
WHERE aTbl.objectkey = bTbl.parentkey
  AND aTbl.flag1 = :NUMBER

SELECT *
FROM
  (
  SELECT A, B, C, flag1
  FROM   aTbl, bTbl
  LEFT JOIN cTbl ON 
    bTbl.cTblID = cTbl.objectkey
  WHERE aTbl.objectkey = bTbl.parentkey
  )
WHERE flag1 = :NUMBER

/*
CREATE VIEW v_abc AS
SELECT A, B, C, flag1 
FROM   aTbl, bTbl
LEFT JOIN cTbl ON 
  bTbl.cTblID = cTbl.objectkey
WHERE aTbl.objectkey = bTbl.parentkey
*/

SELECT A, B, C
FROM v_abc
WHERE flag1 = :NUMBER

the plan will:

该计划将：

1. Be the same (if Oraclewill choose to push the predicate, which is more than probable);
   • Be compiled when first called;
   • Be reused when called again.

1. 是相同的（如果Oracle会选择推送谓词，这很有可能）；
   • 第一次调用时被编译；
   • 再次调用时可以重用。

回答by Juris

Views will not cause performance improvement. Sometimes they may degrade preformance. I don't mean you should avoid views; just know the possible impact before using them. Oracle documentationsays:

视图不会导致性能提升。有时它们可​​能会降低性能。我并不是说你应该避免观点；在使用它们之前知道可能的影响。Oracle 文档说：

Views can speed up and simplify application design. A simple view definition can mask data model complexity from the programmers whose priorities are to retrieve, display, collect, and store data.

However, while views provide clean programming interfaces, they can cause sub-optimal, resource-intensive queries. The worst type of view use is when a view references other views, and when they are joined in queries. In many cases, developers can satisfy the query directly from the table without using a view. Usually, because of their inherent properties, views make it difficult for the optimizer to generate the optimal execution plan.

视图可以加快和简化应用程序设计。一个简单的视图定义可以对那些优先检索、显示、收集和存储数据的程序员屏蔽数据模型的复杂性。

然而，虽然视图提供了干净的编程接口，但它们可能会导致次优、资源密集型查询。最糟糕的视图使用类型是当一个视图引用其他视图时，以及当它们加入查询时。在很多情况下，开发人员可以直接从表中满足查询，而无需使用视图。通常，由于其固有属性，视图使优化器难以生成最佳执行计划。

回答by SquareCog

A view isn't really what you want here.

视图并不是您真正想要的。

What you want to do is use bind variablesfor your flag condition; that way you can compile the statement once, and execute multiple times for different flags.

您想要做的是为您的标志条件使用绑定变量；这样你就可以编译一次语句，并针对不同的标志执行多次。

Just as a stylistic note -- you should decide how you specify joins, and go with that, for consistency and readability. As is, you have the explicit join condition for atbl and btbl, and the "left join" syntax for btbl and ctbl.. doesn't really matter in the scheme of things, but looks and reads a little strange.

就像文体注释一样——您应该决定如何指定连接，并采用这种方式，以保持一致性和可读性。照原样，您有 atbl 和 btbl 的显式连接条件，而 btbl 和 ctbl 的“左连接”语法在事物方案中并不重要，但看起来和读起来有点奇怪。

Good luck!

祝你好运！

回答by user60890

I don't think this is what you are looking for, but if the under lying tables do not change often, like only daily or hourly, you could use a materialized view. That is basically a stored result set. You can also add indexes on them. They can be refreshed on a schedule or when the under lying tables change (not really for OLTP).

我不认为这就是您要寻找的内容，但是如果下面的表不经常更改，例如每天或每小时更改一次，您可以使用物化视图。这基本上是一个存储的结果集。您还可以为它们添加索引。它们可以按计划或在底层表发生变化时刷新（不是真正用于 OLTP）。

Here is Oracle's docs on using materialized views.

这是关于使用物化视图的 Oracle 文档。

回答by ShoeLace

the answer here is YES and NO.

这里的答案是肯定的和否定的。

oracle will only parse a single statement once. the key being that it has to be 'identical' not just similar. this includes spacing (or tabs) and comments.

oracle 只会解析单个语句一次。关键是它必须是“相同的”，而不仅仅是相似。这包括间距（或制表符）和注释。

creating a view with create viewallows you to fix a statement as-is and allow for reuse.

创建视图create view允许您按原样修复语句并允许重用。

however.

然而。

SELECT * FROM VIEW
and
SELECT * FROM VIEW WHERE A=1

SELECT * FROM VIEW
和
SELECT * FROM VIEW WHERE A=1

are 2 different statement/queries.

是 2 个不同的语句/查询。

to get maximum statment reuse you HAVE TO USE bind variables, and not just concatenate conditions onto your SELECT. and that usuall means prepared statments

要获得最大的语句重用，您必须使用绑定变量，而不仅仅是将条件连接到您的 SELECT 上。这通常意味着准备好的声明

you have not mentioned which host language if so say i'll update with a specifica example if i can.

你没有提到哪种宿主语言，如果可以的话，我会用一个具体的例子来更新。

in PL/SQL

在 PL/SQL 中

DECLARE
  cursor c (cpflag aTbl.flag1%TYPE )is
   select A, B, C 
     from aTbl, bTbl, cTbl
    where aTbl.objectkey = bTbl.parentkey
      and bTbl.cTblID = cTbl.objectkey
      and aTbl.flag1 = cp_flag ;

vtype c%rowtype;
BEGIN

open c(100);
fetch c into vtype;
close c;

open c(200);
fetch c into vtype;
close c;
END;

in things like java prepares statments you put "?" in as placholders and use stmt.bind() calls

在诸如java之类的准备语句中，您将“？” 作为占位符并使用 stmt.bind() 调用

hope that helps

希望有帮助

回答by Tony Andrews

There is no "pre-compilation" of views in Oracle: selecting from a defined view is no different performance-wise than selecting from the table directly (assuming all joins and conditions are the same).

Oracle 中没有视图的“预编译”：从定义的视图中选择与直接从表中选择在性能方面没有区别（假设所有连接和条件都相同）。

I am puzzled when you say your query is "being used 10000 times during a request". That doesn't sound optimal - why is that?

当您说您的查询“在请求期间被使用了 10000 次”时，我感到很困惑。这听起来不是最佳选择 - 为什么会这样？