添加内联视图和 ROWNUM 以防止Oracle将查询重写为单个查询块并多次执行这些函数 .
Sample function and demonstration of the problem
create or replace function wait_1_second return number is
begin
execute immediate 'begin dbms_lock.sleep(1); end;';
-- ...
-- Do something here to make caching impossible.
-- ...
return 1;
end;
/
--1 second
select wait_1_second() from dual;
--2 seconds
select wait_1_second(), wait_1_second() from dual;
--3 seconds
select wait_1_second(), wait_1_second() , wait_1_second() from dual;
Simple query changes that do NOT work
这两种方法都需要2秒,而不是1秒 .
select x, x
from
(
select wait_1_second() x from dual
);
with execute_function as (select wait_1_second() x from dual)
select x, x from execute_function;
Forcing Oracle to execute in a specific order
很难告诉Oracle "execute this code by itself, don't do any predicate pushing, merging, or other transformations on it" . 每个优化都有提示,但它们很难使用 . 有几种方法可以禁用这些转换,添加额外的 ROWNUM 通常是最简单的方法 .
--Only takes 1 second
select x, x
from
(
select wait_1_second() x, rownum
from dual
);
很难确切地看到函数的评估位置 . 但是这些解释计划显示 ROWNUM 如何导致内联视图单独运行 .
explain plan for select x, x from (select wait_1_second() x from dual);
select * from table(dbms_xplan.display(format=>'basic'));
Plan hash value: 1388734953
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FAST DUAL | |
---------------------------------
explain plan for select x, x from (select wait_1_second() x, rownum from dual);
select * from table(dbms_xplan.display(format=>'basic'));
Plan hash value: 1143117158
---------------------------------
| Id | Operation | Name |
---------------------------------
| 0 | SELECT STATEMENT | |
| 1 | VIEW | |
| 2 | COUNT | |
| 3 | FAST DUAL | |
---------------------------------