DBA Hub

📋Steps in this guide1/9

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)

Define PL/SQL functions and procedures in the WITH clause of SQL statements.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Setup

The examples in this article require the following test table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM   dual
CONNECT BY level <= 1000000;

-- Gathering stats on a CTAS is no longer necessary in 12c,
-- provided the statement is issued by a non-SYS user.
-- EXEC DBMS_STATS.gather_table_stats(USER, 't1');
2

Functions in the WITH Clause

The declaration section of the clause can be used to define PL/SQL functions, as shown below. Interestingly, the ";" does not seem to work as a terminator to the SQL statement when the PL/SQL declaration is included in the clause. If we attempt to use it on its own, SQL*Plus waits for more text to be entered. Even the example in the SQL Reference manual uses a combination of ";" and "/". From a name resolution perspective, functions defined in the PL/SQL declaration section of the clause take precedence over objects with the same name defined at the schema level.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/

WITH_FUNCTION(ID)
-----------------
                1

SQL>
3

Procedures in the WITH Clause

We can also define procedures in the declaration section, even if they are not used. In reality, you would only put a procedure into a clause if you planned to call the procedure from a function in the declaration section.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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   t1
WHERE  rownum = 1
/

        ID
----------
         1

SQL>

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   t1
WHERE  rownum = 1
/

WITH_FUNCTION(ID)
-----------------
                1

p_id=1
SQL>
4

PL/SQL Support

There does not appear to be any PL/SQL support for this feature. Any attempt to use it results in compilation errors, as shown below. Using dynamic SQL allows you to work around this restriction. Support for this feature using static SQL inside PL/SQL is due in a future release.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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   t1
                  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>

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   t1
            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>
5

Performance Advantages

The whole reason for defining the PL/SQL code inline is to improve performance. Create a regular function to use as a comparison. Run the following test, which measures the elapsed time and CPU usage of the query using the inline function definition. From this we can see the inline function definition takes approximately one third the elapsed time and CPU time to complete.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_id;
END;
/

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   t1';
            
  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   t1';
            
  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>
6

PRAGMA UDF

In a number of presentations prior to the official 12c release, speakers mentioned (User Defined Function), which supposedly gives you the performance advantages of inline PL/SQL, whilst allowing you to define the PL/SQL object outside the SQL statement. The following code redefines the previous normal function to use this pragma. Once the function is compiled, running the test from the previous section against this function produces rather interesting results. The standalone function using PRAGMA UDF seems to consistently out perform the inline function. I was under the impression calling a function defined with directly from PL/SQL would fail. This does not seem to be the case.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_id;
END;
/

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   t1';
            
  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   t1';
            
  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>

DECLARE
  l_number NUMBER;
BEGIN
  l_number := normal_function(1);
END;
/

PL/SQL procedure successfully completed.

SQL>
7

WITH_PLSQL Hint

If the query containing the PL/SQL declaration section is not the top level query, the top-level query must include the hint. Without this hint, the statement will fail to compile, as shown by the following update statement. Adding the hint allows the statement to compile and run as expected. Remember, this is true of queries and DML.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
UPDATE 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);
/
SET a.id = (WITH
            *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause


SQL>

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 Jonathan Lewis pointed out here , the use of functions in the clause prevent the optimizations from taking place in the initial release. The behaviour was fixed in 18c onward. Notice below the elapsed time is down to approximately 1 second. Thanks to Mikhail L for pointing that out.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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   t1
WHERE  ROWNUM <= 10;
/

SLOW_FUNCTION(ID)
-----------------
		1
		1
		1
		1
		1
		1
		1
		1
		1
		1

10 rows selected.

Elapsed: 00:00:10.07
SQL>

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   t1
WHERE  ROWNUM <= 10;
/

SLOW_FUNCTION(ID)
-----------------
		1
		1
		1
		1
		1
		1
		1
		1
		1
		1

10 rows selected.

Elapsed: 00:00:01.071
SQL>
9

Scalar Subquery Caching

In the previous section we saw the negative impact of inline function definitions on the hint optimizations. Fortunately, scalar subquery caching is not adversely affected in the same way. For more information see: - WITH Clause : PL/SQL Declaration Section - WITH Clause : Subquery Factoring - Recursive Subquery Factoring : Hierarchical Queries Using Recursive WITH Clauses - Efficient Function Calls From SQL - Using a PL/SQL Function in the WITH Clause: Examples - UDF Pragma Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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   t1
WHERE  ROWNUM <= 10;
/

(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
				1
				1
				1
				1
				1
				1
				1
				1
				1
				1

10 rows selected.

Elapsed: 00:00:01.04
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!