SQL查询的SELECT子句中的Oracle PL/SQL语句的惰性求值 [英] Lazy evaluation of Oracle PL/SQL statements in SELECT clauses of SQL queries

问题描述

我在游标中使用的Oracle select语句存在性能问题.在该语句中，SELECT子句中的术语之一评估起来很昂贵(这是一个PL/SQL过程调用，它相当大量地访问数据库).但是，WHERE子句和ORDER BY子句很简单.

I have a performance problem with an Oracle select statement that I use in a cursor. In the statement one of the terms in the SELECT clause is expensive to evaluate (it's a PL/SQL procedure call, which accesses the database quite heavily). The WHERE clause and ORDER BY clauses are straightforward, however.

我希望Oracle首先执行WHERE子句以标识与查询匹配的记录集，然后执行ORDER BY子句对它们进行排序，最后评估SELECT子句中的每个术语.当我在要从中提取结果的游标中使用此语句时，我期望仅当需要从游标请求每个结果时，才根据需要执行SELECT项的昂贵评估.

I expected that Oracle would first perform the WHERE clause to identify the set of records that match the query, then perform the ORDER BY clause to order them, and finally evaluate each of the terms in the SELECT clause. As I'm using this statement in a cursor from which I then pull results, I expected that the expensive evaluation of the SELECT term would only be performed as needed, when each result was requested from the cursor.

但是，我发现这不是Oracle使用的顺序.相反，它似乎在执行排序之前为与WHERE子句匹配的每个记录评估SELECT子句中的术语.因此，在从游标返回任何结果之前，将对结果集中的每个结果结果调用代价高昂的过程.

However, I've found that this is not the sequence that Oracle uses. Instead it appears to evaluate the terms in the SELECT clause for each record that matches the WHERE clause before performing the sort. Due to this, the procedure that is expensive to call is called for every result result in the result set before any results are returned from the cursor.

我希望能够尽快从光标中获得第一个结果.谁能告诉我如何说服Oracle在执行排序之前不要评估SELECT语句中的过程调用?

I want to be able to get the first results out of the cursor as quickly as possible. Can anyone tell me how to persuade Oracle not to evaluate the procedure call in the SELECT statement until after the sort has been performed?

这可能更容易在示例代码中描述:

This is all probably easier to describe in example code:

给出具有列a，b，c和d的表example，我有一条语句:

Given a table example with columns a, b, c and d, I have a statement like:

select a, b, expensive_procedure(c)
  from example
 where <the_where_clause>
 order by d;

执行此操作时，即使我将语句作为游标打开并仅从中提取一个结果，对于与WHERE子句匹配的每条记录也会调用expensive_procedure().

On executing this, expensive_procedure() is called for every record that matches the WHERE clause, even if I open the statement as a cursor and only pull one result from it.

我尝试将语句重组为:

select a, b, expensive_procedure(c)
  from example, (select example2.rowid, ROWNUM
                   from example example2
                  where <the_where_clause>
                  order by d)
  where example.rowid = example2.rowid;

内部SELECT语句中出现ROWNUM的情况迫使Oracle首先对其进行评估.这种重组具有预期的性能优势.不幸的是，它并不总是遵守所需的顺序.

Where the presence of ROWNUM in the inner SELECT statement forces Oracle to evaluate it first. This restructuring has the desired performance benefit. Unfortunately it doesn't always respect the ordering that is required.

请明确一点，我知道我不会缩短返回整个结果集所花费的时间.我希望缩短从语句返回前几个结果所花费的时间.我希望在迭代游标结果时花费的时间是渐进的，而不是在返回第一个结果之前全部消耗掉.

Just to be clear, I know that I won't be improving the time it takes to return the entire result set. I'm looking to improve the time taken to return the first few results from the statement. I want the time taken to be progressive as I iterate over the results from the cursor, not all of it to elapse before the first result is returned.

任何甲骨文专家都可以告诉我如何说服甲骨文在必要时停止执行PL/SQL吗?

Can any Oracle gurus tell me how I can persuade Oracle to stop executing the PL/SQL until it is necessary?

推荐答案

为什么要在嵌入式视图中将EXAMPLE与自身连接?为什么不只是:

Why join EXAMPLE to itself in the in-line view? Why not just:

select /*+ no_merge(v) */ a, b, expensive_procedure(c)
from 
( select a, b, c
  from example
  where <the_where_clause>
  order by d
) v;

这篇关于SQL查询的SELECT子句中的Oracle PL/SQL语句的惰性求值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！