使用rownum減少函式呼叫

摘自:oracle高效設計：

1。建立測試表：
create table t1
( prc_chk_key number(9) not null,
prod_key number(12) not null,
cmpt_loc_key number(5) not null,
loc_key number(5) not null,
prc_chk_dt date
)
/

insert into t1 select 2, 3, 4, 5, sysdate
from all_objects where ROWNUM <= 50;

create table t2
( prc_chk_key number(9) not null,
prc_chk_typ_desc varchar2(35) not null,
cmpt_loc_key number(5),
loc_key number(5) not null
)
/

insert into t2 select 2, 'x', 4, 5
from all_objects where ROWNUM <= 50;

CREATE OR REPLACE function F
(v_prod_key IN number default NULL,
v_prc_chk_key IN number default NULL,
v_return IN varchar2 default NULL,
v_want_sr IN varchar2 default NULL,
v_version IN number ) RETURN varchar2
as
begin
dbms_application_info.set_client_info
(userenv('client_info')+1);
return 'x';
end;
/

2。執行如下sql：
exec dbms_application_info.set_client_info(0);

select /*+ use_hash( a11, a12 ) */
a12.prc_chk_typ_desc prc_chk_typ_desc,
a11.prc_chk_dt prc_chk_dt,
a11.cmpt_loc_key cmpt_loc_key,
a11.prod_key upc_prod_key,
a11.loc_key loc_key,
max(F(a11.PROD_KEY,a11.PRC_CHK_KEY, 'QTY', 'D', 1) ),
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1) ),
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY,'CODE','D', 1) ),
max(F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1) )
from t1 a11,
t2 a12
where a11.cmpt_loc_key = a12.cmpt_loc_key
and a11.loc_key = a12.loc_key
and a11.prc_chk_key = a12.prc_chk_key
group by a12.prc_chk_typ_desc, a11.prc_chk_dt,
a11.cmpt_loc_key, a11.prod_key, a11.loc_key;

set autotrace off
select userenv('client_info' ) data from dual;

結果10000，

3。如果修改如下：
exec dbms_application_info.set_client_info(0);

select /*+ USE_HASH( a11, a12 ) */
a12.prc_chk_typ_desc prc_chk_typ_desc,
a11.prc_chk_dt prc_chk_dt,
a11.cmpt_loc_key cmpt_loc_key,
a11.prod_key upc_prod_key,
a11.loc_key loc_key,
max(a),
max(b),
max(c),
max(d)
from (select a11.*,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'QTY', 'D', 1 ) a,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'AMT', 'D',1 ) b,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'CODE', 'D', 1 ) c,
F(a11.PROD_KEY, a11.PRC_CHK_KEY, 'PRC', 'D',1 ) d,
ROWNUM r
from t1 a11 ) a11,
T2 a12
where a11.cmpt_loc_key = a12.cmpt_loc_key
and a11.loc_key = a12.loc_key
and a11.prc_chk_key = a12.prc_chk_key
group by a12.prc_chk_typ_desc, a11.prc_chk_dt,
a11.cmpt_loc_key, a11.prod_key, a11.loc_key;

select userenv('client_info' ) data from dual;

這樣結果200，可以減少函式的呼叫。這個例子透過加入一列rownum偽列，並沒有成指數增長。包含rownum的內嵌試圖將被求值並且實體化，這樣減少了函式的呼叫。如果去掉rownum，或者加入一個1 r 這樣，並不能達到這個目的。