Friday, March 15, 2024

Order of appearance of functions and subqueries in a WITH ... SELECT

I don't know if it is the same for you but I tend to forget certain details, that's why often I need to write down some examples in this place to save time for the future.

For instance one thing that I tend to forget is the order in which you can specify functions and subqueries inside the same WITH. 

Functions must come first, then subqueries, if any.

Here is a working example, two functions, two subqueries and the final SELECT.

with 
   -- first function
function msg ( msgnum in integer) return varchar2
as
 s varchar2(200);
 i pls_integer;
begin
 i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s);
 return 'ORA-'||to_char(msgnum,'TM9')||': '||s;
end;
  -- second function
function msg2 ( msgnum in integer) return varchar2
as
 s varchar2(200); i pls_integer; begin i := utl_lms.get_message(msgnum, 'rdbms', 'ora', 'italian', s); return 'ORA-'||to_char(msgnum,'TM9')||': '||s; end; -- some subqueries x as (select 13367 as a from dual ), y as (select 13368 as a from dual) -- final query select msg(a) from x union all select msg2(a) from y;
According to a performance comparison made by Tim Hall, inline functions seems to be faster than equivalent ordinary functions unless PRAGMA UDF has been specified in the latter, in which case ordinary functions seem to outperform the inline ones.

Adding PRAGMA UDF to inline functions doesn't seem to make any difference.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles