Subquery Factoring or Common Table Expression (CTE)

Q1. What is Subquery Factoring?

Answer. Subquery factoring is implemented with the usage of with clause. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being required by each reference. Thus, it is useful in performance optimization.

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; 

Q2. Explain Procedure & Function in With clause (12c)

Answer.

DROP TABLE t1 PURGE;
CREATE TABLE t1 AS
 SELECT 1 AS id
   FROM dual
CONNECT BY level <= 1000000;
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

One can also define procedures in the declaration section, even if they are not used as shown below.

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

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

It cannot be used in anonymous block but can be used with dynamic SQL using cursors as shown below.

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.

Note: The “;” does not work as a terminator to the SQL statement when the PL/SQL declaration is included in the WITH clause.

Q3. What is PRAGMA UDF (12c)?

Answer. PRAGMA UDF gives you the performance advantages of inline PL/SQL, allowing you to define the PL/SQL object outside the SQL statement as shown below.

CREATE OR REPLACE FUNCTION normal_func(p_id IN NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_id;
END normal_func;

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.

Q4. What is WITH_PLSQL hint (12c)?

Answer. If the query containing the PL/SQL declaration section is not the top-level query, then thee 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 /*+ WITH_PLSQL */ t1 a
    SET a.id = (WITH
                  FUNCTION with_func(p_id IN NUMBER) RETURN NUMBER IS
                  BEGIN
                    RETURN p_id;
                  END;
                SELECT with_func(a.id)
                  FROM dual);
 100 rows updated.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.