In Oracle 12c there is a possibility to define a PL/SQL function inside the "WITH" clause, which can reduce the context switching overhead.
Short example:
SQL> create or replace function F_DUMMY(x number) return number as
2 begin
3 return x+100;
4 end;
5 /
Function created.
SQL> set timing on
SQL> select sum(F_DUMMY(amount_sold))
2 from sales_big;
SUM(F_DUMMY(AMOUNT_SOLD))
------------------------
3041442099
Elapsed: 00:01:50.44
SQL> /
SUM(F_DUMMY(AMOUNT_SOLD))
------------------------
3041442099
Elapsed: 00:01:37.99
SQL> ;
1 with function F_DUMMY2(x number) return number as
2 begin
3 return x+100;
4 end;
5 select sum(F_DUMMY2(amount_sold))
6* from sales_big
SQL> /
SUM(DUPSKO2(AMOUNT_SOLD))
-------------------------
3041442099
Elapsed: 00:00:07.03
SQL> /
SUM(DUPSKO2(AMOUNT_SOLD))
-------------------------
3041442099
Elapsed: 00:00:07.22
48 LINESTEXT / UTF-8