PL/SQL might run faster in SQL - 12c

As of Oracle database 12c, two kinds of plsql functions might run faster in SQL.

        PL/SQL functions declared and defined in the With clause of sql statement.

        PL/SQL functions that are defined with “UDF pragma”

rajesh@PDB1> create table t

  2  as

  3  select *

  4    from all_objects; 

Table created. 

rajesh@PDB1> create or replace function is_number(x varchar2)

  2  return varchar2

  3  as

  4     num_error exception;

  5     pragma exception_init(num_error,-6502);

  6  begin

  7     if to_number(x) is not null then

  8             return 'Y';

  9     else

 10             return 'N';

 11     end if;

 12  exception

 13     when num_error then

 14             return 'N';

 15  end;

 16  /

Function created. 

rajesh@PDB1>

rajesh@PDB1> create or replace function is_number2(x varchar2)

  2  return varchar2

  3  as

  4     num_error exception;

  5     pragma exception_init(num_error,-6502);

  6     PRAGMA UDF;

  7  begin

  8     if to_number(x) is not null then

  9             return 'Y';

 10     else

 11             return 'N';

 12     end if;

 13  exception

 14     when num_error then

 15             return 'N';

 16  end;

 17  / 

Function created.

 

rajesh@PDB1> /*

rajesh@PDB1> set termout off

rajesh@PDB1> select object_id, is_number(object_id) from t ;

rajesh@PDB1>

rajesh@PDB1> with function is_num(x varchar2)

rajesh@PDB1> return varchar2

rajesh@PDB1> as

rajesh@PDB1>    num_error exception;

rajesh@PDB1>    pragma exception_init(num_error,-6502);

rajesh@PDB1> begin

rajesh@PDB1>    if to_number(x) is not null then

rajesh@PDB1>            return 'Y';

rajesh@PDB1>    else

rajesh@PDB1>            return 'N';

rajesh@PDB1>    end if;

rajesh@PDB1> exception

rajesh@PDB1>    when num_error then

rajesh@PDB1>            return 'N';

rajesh@PDB1> end ;

rajesh@PDB1> select object_id, is_num(object_id)        from t ;

rajesh@PDB1> /

rajesh@PDB1>

rajesh@PDB1> select object_id, is_number2(object_id) from t ;

rajesh@PDB1> set termout on

rajesh@PDB1> */

rajesh@PDB1>

rajesh@PDB1>

rajesh@PDB1> 

select object_id, is_number(object_id)

from t 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      597      1.10       1.12       1502       2095          0       89341

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

total      599      1.10       1.12       1502       2095          0       89341

 

with function is_num(x varchar2)

return varchar2

as

       num_error exception;

       pragma exception_ini(num_error,-6502);

begin

       if to_number(x) is not null then

              return 'Y';

       else

              return 'N';

       end if;

exception

       when num_error then

              return 'N';

end ;

select object_id, is_num(object_id)      from t

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.01       0.06          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      597      0.29       0.45       1502       2095          0       89341

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

total      599      0.31       0.52       1502       2095          0       89341

 

 

select object_id, is_number2(object_id)

from  t 

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      597      0.26       0.45       1502       2095          0       89341

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

total      599      0.26       0.46       1502       2095          0       89341