WITH Clause : Subquery Factoring
(WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1))
Related articles.
Subquery Factoring
The
WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
This article shows how the
WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.
Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
WHERE e.deptno = dc.deptno;
Using a
WITH clause this would look like the following.
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
dept_count dc
WHERE e.deptno = dc.deptno;
The difference seems rather insignificant here.
What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc1,
emp m,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
Using the
WITH clause this would look like the following.
WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc1.dept_count AS emp_dept_count,
m.ename AS manager_name,
dc2.dept_count AS mgr_dept_count
FROM emp e,
dept_count dc1,
emp m,
dept_count dc2
WHERE e.deptno = dc1.deptno
AND e.mgr = m.empno
AND m.deptno = dc2.deptno;
So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the
WITH clause, making the query much easier to read.
If the contents of the
WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumentedMATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.WITH dept_count AS ( SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno) SELECT ... WITH dept_count AS ( SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count FROM emp GROUP BY deptno) SELECT ...
Even when there is no repetition of SQL, the
WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.
WITH
dept_costs AS (
SELECT dname, SUM(sal) dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;
In the previous example, the main body of the query is very simple, with the complexity hidden in the
WITH clause.PL/SQL Declaration Section
An Oracle Database 12c enhancement allows PL/SQL declarations in the
WITH clause.WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)
In addition to the Subquery Factoring Clause, Oracle 12c includes a PL/SQL declaration section in the
WITH clause.- Setup
- Functions in the WITH Clause
- Procedures in the WITH Clause
- PL/SQL Support
- Performance Advantages
- PRAGMA UDF
- WITH_PLSQL Hint
- DETERMINISTIC Hint
- Scalar Subquery Caching
Related articles.
Setup
The examples in this article require the following test table.
DROP TABLE t1 PURGE; CREATE TABLE t1 AS SELECT 1 AS id FROM dual CONNECT BY level <= 1000000; COMMIT; -- 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');
Functions in the WITH Clause
The declaration section of the
WITH clause can be used to define PL/SQL functions, as shown below.
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>
Interestingly, the ";" does not seem to work as a terminator to the SQL statement when the PL/SQL declaration is included in the
WITH 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
WITH clause take precedence over objects with the same name defined at the schema level.Procedures in the WITH Clause
We can also define procedures in the declaration section, even if they are not used.
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>
In reality, you would only put a procedure into a
WITH clause if you planned to call the procedure from a function in the declaration 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 t1
WHERE rownum = 1
/
WITH_FUNCTION(ID)
-----------------
1
p_id=1
SQL>
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.
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>
Using dynamic SQL allows you to work around this restriction.
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>
Support for this feature using static SQL inside PL/SQL is due in a future release.
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.
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS BEGIN RETURN p_id; END; /
Run the following test, which measures the elapsed time and CPU usage of the query using the inline function definition.
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>
From this we can see the inline function definition takes approximately one third the elapsed time and CPU time to complete.
PRAGMA UDF
In a number of presentations prior to the official 12c release, speakers mentioned
PRAGMA UDF (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.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 test from the previous section against this function produces rather 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 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>
The standalone function using PRAGMA UDF seems to consistently out perform the inline function.
I was under the impression calling a function defined with
PRAGMA UDF directly from PL/SQL would fail. This does not seem to be the case.DECLARE l_number NUMBER; BEGIN l_number := normal_function(1); END; / PL/SQL procedure successfully completed. SQL>
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
WITH_PLSQL hint. Without this hint, the statement will fail to compile, as shown by the following update statement.
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>
Adding the
WITH_PLSQL hint allows the statement to compile and run 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>
DETERMINISTIC Hint
As Jonathan Lewis pointed out here, the use of functions in the
WITH clause prevent the DETERMINISTIC optimizations from taking place.
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>
So be careful that in trying to improve performance, you don't actually decrease it. This is mostly likely a bug, so retest on future releases to make sure this still applies.
Scalar Subquery Caching
In the previous section we saw the negative impact of inline function definitions on the
DETERMINISTIC hint optimizations. Fortunately, scalar subquery caching is not adversely affected in the same way.
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>
For more info Refer: http://www.oracle-base.com/articles/12c/with-clause-enhancements-12cr1.php
No comments:
Post a Comment