📋Steps in this guide1/4

WITH Clause : Subquery Factoring in Oracle

Use the WITH clause in Oracle to reduce repetition and simplify complex SQL statements.

oracle miscconfigurationintermediate

by OracleDba

56 views

Setup

The examples below use the following tables.

Code/Command (click line numbers to comment):

-- drop table emp purge;
-- drop table dept purge;

create table dept (
  deptno number(2) constraint pk_dept primary key,
  dname varchar2(14),
  loc varchar2(13)
) ;

create table emp (
  empno number(4) constraint pk_emp primary key,
  ename varchar2(10),
  job varchar2(9),
  mgr number(4),
  hiredate date,
  sal number(7,2),
  comm number(7,2),
  deptno number(2) constraint fk_deptno references dept
);

insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');

insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;

Subquery Factoring

The 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 clause on a case-by-case basis. This article shows how the 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. Using a clause this would look like the following. 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. Using the clause this would look like the following. So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the clause, making the query much easier to read. If the contents of the 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 and optimizer hints can be used to influence the decision. The undocumented hint tells the optimizer to resolve the subquery as a global temporary table, while the hint tells it to process the query inline. Even when there is no repetition of SQL, the clause can simplify complex queries, like the following example that lists those departments with above average wages. In the previous example, the main body of the query is very simple, with the complexity hidden in the clause.

Code/Command (click line numbers to comment):

-- Non-ANSI Syntax
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;

-- ANSI Syntax
select e.ename as employee_name,
       dc.dept_count as emp_dept_count
from   emp e
       join (select deptno, count(*) as dept_count
             from   emp
             group by deptno) dc
         on e.deptno = dc.deptno;

-- Non-ANSI Syntax
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;

-- ANSI Syntax
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
       join dept_count dc on e.deptno = dc.deptno;

-- Non-ANSI Syntax
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;

-- ANSI Syntax
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
       join (select deptno, count(*) as dept_count
             from   emp
             group by deptno) dc1
         on e.deptno = dc1.deptno
       join emp m on e.mgr = m.empno
       join (select deptno, count(*) as dept_count
             from   emp
             group by deptno) dc2
         on m.deptno = dc2.deptno;

-- Non-ANSI Syntax
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;

-- ANSI Syntax
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
       join dept_count dc1 on e.deptno = dc1.deptno
       join emp m on e.mgr = m.empno
       join dept_count dc2 on m.deptno = dc2.deptno;

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 ...

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;

MATERIALIZE Hint

The undocumented hint was mentioned above, but there seems to be a little confusion over how it is implemented. We can see what is happening under the covers using SQL trace. Create a test table. Check the trace file location. Trace a statement using the hint. The following abbreviated output shows some points of interest in the resulting trace file. Notice the "CREATE GLOBAL TEMPORARY T" and "TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB" lines. It certainly seems to be using a global temporary table. This is an undocumented feature. I've given you an example that uses a global temporary table, but perhaps there are other circumstances that don't.

Code/Command (click line numbers to comment):

conn test/test

create table t1 as
select level as id
from   dual
connect by level <= 100;

select value
from   v$diag_info
where  name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_4278.trc

SQL>

exec dbms_monitor.session_trace_enable;

with query1 as (
  select /*+ materialize */ * from t1
)
select * from query1;

exec dbms_monitor.session_trace_disable;

=====================
PARSING IN CURSOR #140100560521424 len=174 dep=1 uid=0 oct=1 lid=0 tim=733844612 hv=1878591410 ad='80b179f0' sqlid='40a2untrzk1xk'
CREATE GLOBAL TEMPORARY T
END OF STMT
...
=====================
...
=====================
PARSING IN CURSOR #140100560423976 len=77 dep=0 uid=109 oct=3 lid=109 tim=733865863 hv=3518560624 ad='a35bc6c0' sqlid='9fzhbw78vjybh'
WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t1
)
SELECT * FROM query1
END OF STMT
...
STAT #140100560423976 id=1 cnt=100 pid=0 pos=1 obj=0 op='TEMP TABLE TRANSFORMATION  (cr=15 pr=1 pw=1 time=19589 us)'
STAT #140100560423976 id=2 cnt=0 pid=1 pos=1 obj=0 op='LOAD AS SELECT  (cr=3 pr=0 pw=1 time=16243 us)'
STAT #140100560423976 id=3 cnt=100 pid=2 pos=1 obj=91676 op='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=1514 us cost=3 size=300 card=100)'
STAT #140100560423976 id=4 cnt=100 pid=1 pos=2 obj=0 op='VIEW  (cr=12 pr=1 pw=0 time=1257 us cost=2 size=1300 card=100)'
STAT #140100560423976 id=5 cnt=100 pid=4 pos=1 obj=4254950955 op='TABLE ACCESS FULL SYS_TEMP_0FD9D662B_2E34FB (cr=12 pr=1 pw=0 time=1203 us cost=2 size=300 card=100)'
...
=====================

PL/SQL Declaration Section

An Oracle Database 12c enhancement allows PL/SQL declarations in the clause. This enhancement is discussed here . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!