Interesting New WITH Clause Feature in Oracle 12c.

#TGIF
In Oracle 12c, WITH clause is capable to have PL-SQL type Function or procedure inline with underlying SQL.
Function or Procedural within WITH clause is scope till SQL Execution lifetime and cease to exist after execution completion.

Having Function as part of WITH clause has it owns advantages, some as below.

1. Inline function within SQL reduces traditional Context Switch between SQL Engine and Pl-SQL Engine.
2. Computational function specific to a SQL can be inline instead of being an object in database Schema.
3. For Read only database, we can have Function inline with SQL.
4. WITH clause help SQL to use programming logic as with PLSQL (Pls. Note := it has some limitations.)
5. We can test a function without creating it as part of database schema.
6. Procedural or Function within WITH clause get priority over objects defined with same name in database schema.

Lets start with some example around New WITH clause.
We will create function as part of WITH clause and use same in underlying SQL.

WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 + 10;
END;
FUNCTION FN_ADD_11(OPERAND1 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 + 11;
END;
SELECT FN_ADD_10(1) ADD_10,FN_ADD_11(1) ADD_11 FROM DUAL;

ADD_10 ADD_11
———- ———-
11               12

Now lets evaluate different scenario and get deep understanding of WITH Clause.
Demonstrate SQL were not working on my VM SQL Developer version (Version 4.1.1.19), So for demonstrate purpose let use Oracle Live SQL.
Oracle Live SQL :: Nice online console to play around SQL with Oracle latest version.

1. WITH_PLSQL Hint.
If With clause has a function/procedure and it is not the Top level declaration then SQL fails.

with_plsql

Not Sure, why getting Invalid Statement while running same in Oracle Live SQL.please ignore same.

SELECT * FROM ( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10; END; SELECT FN_ADD_10(2) FROM DUAL);

SQL will failed with below Error.
ORA-32034: unsupported use of WITH clause

To avoid such error, use WITH_PLSQL hint.
assuming it is some sort of compiler directive to tell oracle, WITH clause is not top level declaration but part of SQL with function.

SELECT /*+ WITH_PLSQL*/* FROM ( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10; END; SELECT FN_ADD_10(2) FROM DUAL)


output :
FN_ADD_10(2)
----------
12

2.Priority over function defined in Schema
Creating function with same name “FN_ADD_10”, but subtracting 10 from input variable.

CREATE OR REPLACE FUNCTION FN_ADD_10(
OPERAND1 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN OPERAND1 - 10;
END;

within SQL will use same Name function as part of WITH clause.

SELECT
/*+ WITH_PLSQL*/
ADD_10 ,
FN_ADD_10(2)
FROM
( WITH FUNCTION FN_ADD_10(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN OPERAND1 + 10;
END;
SELECT FN_ADD_10(2) AS ADD_10 FROM DUAL
)

Function as part of WITH clause is consider over schema level functions.
we can use similar method for any testing before implementing same at schema level.

with_plsql_1

3.Context Switch Performance
As function defined is inline it give advantage over context switch betn SQL and PLSQL engine.

SELECT SYSTIMESTAMP FROM DUAL;
DECLARE
L_CURSOR SYS_REFCURSOR;
TYPE T_TAB IS TABLE OF NUMBER;
L_TAB T_TAB;
BEGIN
DBMS_OUTPUT.PUT_LINE('WITHOUT WITH CLAUSE');
OPEN L_CURSOR FOR 'SELECT FN_ADD_DD(ROWNUM) COL1 FROM DUAL CONNECT BY ROWNUM < 1000000';
FETCH L_CURSOR BULK COLLECT INTO L_TAB;

CLOSE L_CURSOR;
END;
SELECT SYSTIMESTAMP FROM DUAL;
DECLARE
L_CURSOR SYS_REFCURSOR;
TYPE T_TAB IS TABLE OF NUMBER;
L_TAB T_TAB;
BEGIN
DBMS_OUTPUT.PUT_LINE(' WITH CLAUSE');
OPEN L_CURSOR FOR 'WITH FUNCTION FN_ADD_DD(OPERAND1 NUMBER) RETURN NUMBER IS BEGIN RETURN (OPERAND1 - TO_NUMBER(TO_CHAR(SYSDATE , ''DD''))); END; SELECT FN_ADD_DD(ROWNUM) COL1 FROM DUAL CONNECT BY ROWNUM < 1000000';
FETCH L_CURSOR BULK COLLECT INTO L_TAB;

CLOSE L_CURSOR;
END;
SELECT SYSTIMESTAMP FROM DUAL;

with_plsql_2

Traditional function took roughly 11 sec and with inline function it took 5 sec.
With advantages, it also has many restriction and limitations.it is always wise to test and test and test before making any permanent changes.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle 12c New Features and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s