During mass query and insertion, the performance of custom functions is not good.

Insert into t_subid_basic.pdf
Select cc. subscriberid, CC. offprice
From (select a. subscriberid,
B. favrulepri,
B. offprice,
Rank () over (partition by A. subscriberid order by B. favrulepri DESC) num
From t_subrelationreads A, t_fav_cond_price B
Where a. subscriberid = nvl (B. subscriberid, A. subscriberid)
And a. usertype = nvl (B. subtype, A. usertype)
And a. mastermachine = nvl (B. mastermachine, A. mastermachine)
And decode (B. seqoperator,
'= ',
Decode (sign (A. seqno-nvl (B. seqno, A. seqno), 0, 1, 0 ),
'<= ',
Decode (sign (A. seqno-nvl (B. seqno, A. seqno),-1, 1, 0 ),
'> = ',
Decode (sign (A. seqno-nvl (B. seqno, A. seqno), 1, 1, 0 ),
1) = 1
And a. customercode = nvl (B. customerid, A. customercode)
And a. customertype = nvl (B. customertype, A. customertype)
And a. customerclass = nvl (B. customerclass, A. customerclass)
And B. productcode = '000000') CC
Where cc. num = 1;

 

 

Decode (B. seqoperator,
'= ',
Decode (sign (A. seqno-nvl (B. seqno, A. seqno), 0, 1, 0 ),
'<= ',
Decode (sign (A. seqno-nvl (B. seqno, A. seqno),-1, 1, 0 ),
'> = ',
Decode (sign (A. seqno-nvl (B. seqno, A. seqno), 1, 1, 0 ),
1) = 1

Function functions can be replaced, for example:

Create or replace function f_compare_varchar
(Srcstr in varchar2, -- source character
Bytes in varchar2, -- operator
Deststr in varchar2 -- target character
) Return number
Flag number;
Begin
If srcstr is null or condition is null or deststr is null then
Return 1;
End if;

Execute immediate 'select case when' | srcstr | reverse | deststr |
'Then 1 else 0 end' | CHR (13) | 'from dual'
Into flag;
Return flag;
End f_compare_varchar;

 

However, the performance is poor. It is mainly inserted. The query is not understandable.

In this case, the performance of the function with its own band is good.