Oracle With Clause - Common Table Expressions Version 21c
General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name.
Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.
You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.
A WITH clause is most valuable when the result of the WITH query is required more than one time in the body of the main query such as where one averaged value needs to be compared against two or three times.
The point is to minimize the number of accesses to a table joined multiple times into a single query.
Restrictions on Subquery Factoring:
You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause.
However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.
In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause.
Basic
Single alias
WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM <alias>;
WITH q AS (SELECT dummy FROM dual)
SELECT dummy FROM q;
Double alias
(corrected per notes from (Colin 't Hart and Roy Fraties)
WITH <alias_one> AS
(subquery_sql_statement)
<alias_two> AS
(sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
WITH qb1 AS
(SELECT inst_id FROM gv$session),
qb2 AS
(SELECT unique inst_id FROM qb1
UNION ALL
SELECT unique inst_id FROM qb1)
SELECT /*+ MATERIALIZE */ *
FROM qb1, qb2
WHERE qb1.inst_id = qb2.inst_id;
Advanced
Recursive WITH Clause
WITH <alias> AS
(subquery_sql_statement)
SEARCH <BREADTH | DEPTH> FIRST BY <column_name> [ASC | DESC] [NULLS FIRST | NULLS LAST]
SET <ordering_column>
CYCLE (alias) SET <cycle_mark_alias> TO <cycle_value> DEFAULT <no_cycle_value>
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS (
SELECT employee_id, last_name, manager_id, 0 reportLevel, hire_date, job_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.last_name, e.manager_id,
r.reportLevel+1 reportLevel, e.hire_date, e.job_id
FROM dup_hiredate r, employees e
WHERE r.eid = e.manager_id)
SEARCH DEPTH FIRST BY hire_date SET order1
CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N'
SELECT LPAD(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle
FROM dup_hiredate
ORDER BY order1;
WITH emp_count
(eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS (
SELECT employee_id, last_name, manager_id, 0 mgrLevel, salary, 0 cnt_employees
FROM employees
UNION ALL
SELECT e.employee_id, e.last_name, e.manager_id, r.mgrLevel+1 mgrLevel, e.salary, 1
cnt_employees
FROM emp_count r, employees e
WHERE e.employee_id = r.mgr_id)
SEARCH DEPTH FIRST BY emp_last SET order1
SELECT emp_last, eid, mgr_id, salary, SUM(cnt_employees), MAX(mgrLevel) mgrLevel
FROM emp_count
GROUP BY emp_last, eid, mgr_id, salary
HAVING MAX(mgrLevel) > 0
ORDER BY mgr_id NULLS FIRST, emp_last;
Demo
Mailing list code
EXPLAIN PLAN FOR
SELECT per_h_email
FROM person p
WHERE p.per_ok2_email = 'A'
AND p.per_h_email IN (
SELECT person_id
FROM person
WHERE per_db = 'Y')
UNION
SELECT po_w_email
FROM poie o
WHERE o.po_status = 'A'
AND o.po_w_email IN (
SELECT person_id
FROM person
WHERE per_db = 'Y');
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
WITH w AS (
SELECT person_id
FROM person
WHERE per_db = 'Y')
SELECT per_h_email
FROM person p, w
WHERE p.person_id = w.person_id
AND p.per_ok2_email = 'A'
AND p.per_h_email IS NOT NULL
UNION
SELECT po_w_email
FROM poie o, w
WHERE o.person_id = w.person_id
AND o.po_status = 'A'
AND o.po_w_email IS NOT NULL;
SELECT * FROM TABLE(dbms_xplan.display);
WITH with CONNECT BY
Built from code posted by Michel Cadot at comp.databases.oracle.misc 28-Sep-2006
INSERT INTO t1 VALUES ('Morgan',2);
INSERT INTO t1 VALUES ('Dan',3);
INSERT INTO t1 VALUES ('Edward',2);
INSERT INTO t1 VALUES ('Helen',1);
COMMIT;
SELECT * FROM t1;
CREATE TABLE t2 AS
SELECT pname FROM t1
WHERE 1=2;
SELECT * FROM t2;
INSERT INTO t2
WITH rn AS (
SELECT rownum rn
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(cases) FROM t1))
SELECT pname
FROM t1, rn
WHERE rn <= cases
ORDER BY pname;
SELECT * FROM t2;
Another WITH Clause with CONNECT BY from Michel Cadot
VARIABLE liste VARCHAR2(100)
EXECUTE :liste := '5, 25, 41, 52';
WITH liste AS (
SELECT SUBSTR(:liste, INSTR(','||:liste||',', ',', 1, rn),
INSTR(','||:liste||',', ',', 1, rn+1) -
INSTR(','||:liste||',', ',', 1, rn)-1)
valeur
FROM (
SELECT ROWNUM rn FROM dual
CONNECT BY LEVEL<=LENGTH(:liste) - LENGTH(REPLACE(:liste,',',''))+1))
SELECT TRIM(valeur)
FROM liste;
Another WITH Clause demo based on code posted by David Fitzjarrell at c.d.o.server 01-AUG-2006
INSERT INTO test VALUES (1, 1, 'SENT', SYSDATE-5);
INSERT INTO test VALUES (2, 1, 'RECV', SYSDATE-4);
INSERT INTO test VALUES (3, 2, 'SENT', SYSDATE-3);
INSERT INTO test VALUES (4, 2, 'RECV', SYSDATE-2);
INSERT INTO test VALUES (5, 3, 'SENT', SYSDATE-1);
WITH col_generator AS (
SELECT t1.batch_id, DECODE(t1.action, 'SENT', t1.actdate) sent,
DECODE(t2.action,'RECV', t2.actdate) received
FROM test t1, test t2
WHERE t2.batch_id(+) = t1.batch_id)
SELECT batch_id, max(sent) sent, max(received) received
FROM col_generator
GROUP BY batch_id
ORDER BY 1;
Another WITH Clause demo posted by Maxim Demenko to
comp.databases.oracle.misc 3-Mar-2008.
This demo takes an input of numbers, in seconds and returns minutes:seconds.
col M:S format a10
WITH t AS (
SELECT 100 s FROM dual
UNION ALL
SELECT 7201 FROM dual)
SELECT s,TRUNC(s/60)||':'||mod(s,60) "M:S"
FROM t;
Another WITH Clause demo ... this one provided by H.J. Lofstrom via email on 30-Aug-2011.
This demo shows how to use a CTE to provide the equivalent of multiple input parameters to a query outside of a stored procedure.
WITH date_params AS (SELECT SYSDATE - 30 AS startdate, SYSDATE AS enddate FROM dual)
SELECT do.object_name, 'TABLE'
FROM dba_objects do, date_params dp
WHERE do.object_type = 'TABLE'
AND created BETWEEN dp.startdate AND dp.enddate
UNION ALL
SELECT do.object_name, 'INDEX'
FROM dba_objects do, date_params dp
WHERE do.object_type = 'INDEX'
AND created BETWEEN dp.startdate AND dp.enddate;
