Enhancement of the WITH clause of new features and functions in oracle12c

1. Settings
Create a test table.
DROP TABLE test PURGE;


CREATE TABLE Test AS
SELECT 1 as ID
From dual
CONNECT by Level <= 1000000;


2. Functions in the WITH clause
The WITH Clause Declaration section can be used to define a function, as shown below.
With
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (ID)
From Test
WHERE rownum = 1
/


With_function (ID)
-----------------
1


Sql>
Interestingly, when a PL/SQL declaration is included in the WITH clause, the semicolon ";" Can no longer be used as a terminating character for SQL statements. If we use it, Sql*plus will wait for more command text input. Even in official documents, a semicolon is used ";" and a backslash "/" combination.
From a name resolution perspective, the WITH clause PL/SQL Declaration section defines a function that is higher than other objects of the same name in the current pattern.
3. Procedures in the WITH clause
Even if it is not used, we can define the process in the Declarations section.
SET Serveroutput on


With
PROCEDURE with_procedure (p_id in number) is
BEGIN
Dbms_output.put_line (' p_id= ' | | p_id);
END;
SELECT ID
From Test
WHERE rownum = 1
/


Id
----------
1


Sql>
In reality, if you are going to invoke a procedure from a function in the declaration section, you can define a procedure in the Declarations section.
With
PROCEDURE with_procedure (p_id in number) is
BEGIN
Dbms_output.put_line (' p_id= ' | | p_id);
END;


FUNCTION with_function (p_id in number) RETURN number is
BEGIN
With_procedure (P_ID);
RETURN p_id;
END;
SELECT with_function (ID)
From Test
WHERE rownum = 1
/


With_function (ID)
-----------------
1


P_id=1
Sql>
4. PL/SQL support
PL/SQL does not support this feature. If the view is used in PL/SQL, a compilation error is reported, as shown below.
BEGIN
For Cur_rec in (with
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (ID)
From Test
WHERE rownum = 1)
LOOP
NULL;
END LOOP;
END;
/
FUNCTION with_function (p_id in number) RETURN number is
*
ERROR at line 3:
Ora-06550:line 3, Column 30:
pl/sql:ora-00905:missing keyword
Ora-06550:line 2, Column 19:
Pl/sql:sql Statement ignored
Ora-06550:line 5, Column 34:
Pls-00103:encountered the symbol ";" When expecting one of the following:
Loop




Sql>
Use dynamic SQL to bypass this limitation.
SET Serveroutput on
DECLARE
L_sql VARCHAR2 (32767);
L_cursor Sys_refcursor;
L_value number;
BEGIN
L_sql: = ' with
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (ID)
From Test
WHERE rownum = 1 ';

OPEN l_cursor for L_sql;
FETCH l_cursor into L_value;
Dbms_output.put_line (' l_value= ' | | l_value);
CLOSE L_cursor;
END;
/
L_value=1


PL/SQL procedure successfully completed.


Sql>
The use of this feature in PL/SQL is something of a future release.
5. Performance Benefits
The reason for defining the in-line PL/SQL code is to improve performance. The following creates a general function to compare.
CREATE OR REPLACE FUNCTION normal_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
/
Run the following tests to measure the time and CPU consumed by the in-line function query.
SET Serveroutput on
DECLARE
L_time Pls_integer;
L_CPU Pls_integer;

L_sql VARCHAR2 (32767);
L_cursor Sys_refcursor;

TYPE T_tab is TABLE of number;
L_tab T_tab;
BEGIN
L_time: = Dbms_utility.get_time;
L_CPU: = Dbms_utility.get_cpu_time;


L_sql: = ' with
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (ID)
From Test ';

OPEN l_cursor for L_sql;
FETCH L_cursor
BULK COLLECT into L_tab;
CLOSE L_cursor;

Dbms_output.put_line (' with_function: ' | |
' Time= ' | | To_char (dbms_utility.get_time-l_time) | | ' Hsecs ' | |
' CPU time= ' | | (DBMS_UTILITY.GET_CPU_TIME-L_CPU) | | ' Hsecs ');


L_time: = Dbms_utility.get_time;
L_CPU: = Dbms_utility.get_cpu_time;


L_sql: = ' SELECT normal_function (ID)
From Test ';

OPEN l_cursor for L_sql;
FETCH L_cursor
BULK COLLECT into L_tab;
CLOSE L_cursor;

Dbms_output.put_line (' normal_function: ' | |
' Time= ' | | To_char (dbms_utility.get_time-l_time) | | ' Hsecs ' | |
' CPU time= ' | | (DBMS_UTILITY.GET_CPU_TIME-L_CPU) | | ' Hsecs ');

END;
/
with_function:time=45 hsecs CPU time=39 hsecs
normal_function:time=129 hsecs CPU time=113 hsecs


PL/SQL procedure successfully completed.


Sql>
As you can see from this test, the value of the inline function consumes the time and CPU of the normal function One-third.
6. PRAGMA UDF
Before the 12c version, it was often mentioned that the pragma UDF was said to improve performance through in-row PL/SQL, while also allowing the definition of PL + + objects outside of the statement. The following code re-defines the previous normal function with pragma.
CREATE OR REPLACE FUNCTION normal_function (p_id in number) RETURN number is
PRAGMA UDF;
BEGIN
RETURN p_id;
END;
/
Once the function is compiled, running the function from the previous section produces quite interesting results.
SET Serveroutput on
DECLARE
L_time Pls_integer;
L_CPU Pls_integer;

L_sql VARCHAR2 (32767);
L_cursor Sys_refcursor;

TYPE T_tab is TABLE of number;
L_tab T_tab;
BEGIN
L_time: = Dbms_utility.get_time;
L_CPU: = Dbms_utility.get_cpu_time;


L_sql: = ' with
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (ID)
From Test ';

OPEN l_cursor for L_sql;
FETCH L_cursor
BULK COLLECT into L_tab;
CLOSE L_cursor;

Dbms_output.put_line (' with_function: ' | |
' Time= ' | | To_char (dbms_utility.get_time-l_time) | | ' Hsecs ' | |
' CPU time= ' | | (DBMS_UTILITY.GET_CPU_TIME-L_CPU) | | ' Hsecs ');


L_time: = Dbms_utility.get_time;
L_CPU: = Dbms_utility.get_cpu_time;


L_sql: = ' SELECT normal_function (ID)
From Test ';

OPEN l_cursor for L_sql;
FETCH L_cursor
BULK COLLECT into L_tab;
CLOSE L_cursor;

Dbms_output.put_line (' normal_function: ' | |
' Time= ' | | To_char (dbms_utility.get_time-l_time) | | ' Hsecs ' | |
' CPU time= ' | | (DBMS_UTILITY.GET_CPU_TIME-L_CPU) | | ' Hsecs ');

END;
/
with_function:time=44 hsecs CPU time=40 hsecs
normal_function:time=33 hsecs CPU time=29 hsecs


PL/SQL procedure successfully completed.


Sql>
The standalone function with the pragma UDF seems to have been faster than the inline function.
I thought calling the function defined by the pragma udf from PL/SQL would fail, but the fact seems to be not the case.
DECLARE
L_number number;
BEGIN
L_number: = normal_function (1);
END;
/


PL/SQL procedure successfully completed.


Sql>
7. With_plsql Hint
If the query that contains the PL/SQL Declarations section is not a top-level query, then the top-level query must contain WITH_PLSQL hint. Without the hint, the statement fails at compile time, as shown below.
UPDATE Test A
SET a.id = (with
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (a.id)
from dual);
/
SET a.id = (with
*
ERROR at line 2:
ora-32034:unsupported use of with clause




Sql>
After adding WITH_PLSQL hint, the statement compiles and runs as expected.
UPDATE/*+ with_plsql */T1 A
SET a.id = (with
FUNCTION with_function (p_id in number) RETURN number is
BEGIN
RETURN p_id;
END;
SELECT with_function (a.id)
from dual);
/


1000000 rows updated.


Sql>
8. Deterministic Hint
As Lewis points out, the use of functions in the WITH clause prevents deterministic optimizations from occurring.
SET TIMING on ARRAYSIZE 15


With
FUNCTION slow_function (p_id in number) RETURN number deterministic is
BEGIN
Dbms_lock.sleep (1);
RETURN p_id;
END;
SELECT slow_function (ID)
From Test
WHERE ROWNUM <= 10;
/


Slow_function (ID)
-----------------
1
1
1
1
1
1
1
1
1
1


Rows selected.


elapsed:00:00:10.07
Sql>
9. Scalar Quantum query buffering
In the previous section, we see the negative effects of inline function definitions on deterministic hint optimizations. Fortunately, the scalar quantum query buffer is not affected as well.
SET TIMING on


With
FUNCTION slow_function (p_id in number) RETURN number deterministic is
BEGIN
Dbms_lock.sleep (1);
RETURN p_id;
END;
Select (select Slow_function (ID) from dual)
From Test
WHERE ROWNUM <= 10;
/


(Selectslow_function (ID) fromdual)
---------------------------------
1
1
1
1
1
1
1
1
1
1


Rows selected.


elapsed:00:00:01.04
Sql>

Enhancement of the WITH clause of new features and functions in oracle12c