Beyond the basic select statement: Using the WITH clause inline SUBQUERY

We looked at the with statement with embedded PL/SQL, now lets look at another use of the with statement. This time we are going to use it with embedded subqueries.

Query A This query searches a stocks table, returns the median closing price for a quarter, the standard deviation and plus and minus one standard deviation.

The top two arrows point to the subquerys and the bottom arrow points to referencing the subqueries.  The subqueries are named SDEV and MED, those can then be referenced in the from clause.

image

Query B shows the normal subquery we are accustom to seeing and returns the same values as Query A.

image

Query A and Query B are equivalent.

So, what execution plan did the CBO come up with for Query A

image

And what plan did the CBO come up with for Query B

image

I’ll dig into how the CBO came up with these execution plans later.

Beyond the basic select statement: Using the WITH clause inline PL/SQL

For years I used the basic select statement:

SELECT A.C1, A.C2, B.C1, B.C2, <…> FROM TAB1 A, TAB2 B WHERE A.C1 = B.C1;

Sometimes this basic select statement would not answer my question. You can make a select statement quite complicated, but then it gets unreadable and difficult to maintain. The WITH clause helps out quite a bit

A

B

WITH
FUNCTION monthly_amt(pamt number)
RETURN number IS
x number;
   BEGIN
     x := pamt*2;
     RETURN x;
   END;
SELECT pay22, monthly_amt(pay22)
  FROM policys;

create or replace function
  monthly_amt (pamt number) return number is
  x number;
BEGIN
  x := pamt*2;
  return x;
END;
/
select pay22, monthly_amt(pay22)
from policys;

We are going to start this discussion with the assumption that Query A and Query B are equivalent with caveats. The rules associated with using an inline function is the inline function will always take precedence over a stored function.

The results of Query A returned pay22 and the result of the inline function monthly_amt.

p1

Query B Returns pay22 and the results of the stored function monthly_amt.

p2

But what if the stored function returns a different value the argument * 2?

First we will redefine the stored function monthly_amt to return argument * 10. When using WITH to include an inline function, the inline function will always take precedence over a stored function with the same name.

p3

Now we will call the stored function monthly_amt and not making any reference to an inline function. As expected the stored function returns argument * 10.

p4

How would we use this? PL/SQL supports overloading functions; however both the stored function and the inline function have the same name and use the same arguments. I see using the inline function when: A) creating a stored function is not an option. I worked in one shop a while back where it was against policy to store PL/SQL in the database. B) when you have to overload a stored function. C) When you need / want to read the PL/SQL being executed.