java中利用oracle查询两次,Oracle性能：查询执行多个相同的函数调用

添加内联视图和 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 | |

---------------------------------