Subquery Factoring Clause - WITH

Through general experience I've found the WITH statement either underutilised or people haven't been aware of it's existence, although that seems to be changing - it has been around since 9i.

For me, two typical examples come to mind.
1) I was demonstrating recently an example of using SUBSTR & INSTR together to extract certain parts of a string. I built up the expressions step by step, then to test the example with a slightly different string, I would normally be forced to do a search and replace. Depending on the example, this can be annoying.

select 'halls head 6065'
      ,substr('halls head 6065',4,5) guess
      ,instr('halls head 6065',' ',-1,1) pos_of_space
      ,substr('halls head 6065',1,instr('halls head 6065',' ',-1,1)-1 )||'*' suburb
      ,'*'||substr('halls head 6065',instr('halls head 6065',' ',-1,1)+1 )||'*' postcode
from dual;

Instead I can put the string I want to play with in a simple WITH statement, and change the "column name" at will.

with test as (select 'halls head 6065' suburb from dual)
select suburb
      ,substr(suburb,4,5) guess
      ,instr(suburb,' ',-1,1) pos_of_space
      ,substr(suburb,1,instr(suburb,' ',-1,1)-1 )||'*' suburb
      ,'*'||substr(suburb,instr(suburb,' ',-1,1)+1 )||'*' postcode
from test;

Other times recently I have used the WITH statement to essentially encapsulate a unit of work. I had extracted a SQL statement from some PL/SQL and wanted to run it with my own restrictions, but leave the original untouched - this allowed me to test without worrying if I've disrupted the original join behaviour.

WITH original as ({copy of embedded sql})
select *
from original
where {my own restrictions}

These are ad hoc reasons, there is also a potential performance benefit from using the WITH clause.

I'm sure I've seen an example where using the WITH clause provided a performance benefit, but memory is fallible so I thought I'd see what the documentation had to say.
It says it will give the optimiser more choice about what to do, and that we can improve the query by using the WITH syntax.
After comparing explain plans from the example straight out of the documentation, I wasn't convinced. So I ran timings over different versions of the database - 1000 iterations each. I was surprised at the results (sounds like tabloid journalism, doesn't it?):

Without the clause

SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING
SUM(sal) >
  (
    SELECT SUM(sal) * 1/3
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
  )
ORDER BY SUM(sal) DESC;

With the clause

WITH summary AS
(
 SELECT dname, SUM(sal) AS dept_total
 FROM emp, dept
 WHERE emp.deptno = dept.deptno
 GROUP BY dname
)
SELECT dname, dept_total
FROM summary
WHERE dept_total >
  (
    SELECT SUM(dept_total) * 1/3
    FROM summary
  )
ORDER BY dept_total DESC;

--9i
.3 secs
.0003 secs per iteration
2.9 secs
.0029 secs per iteration
--10g
1.1 secs
.0011 secs per iteration
4.06 secs
.00406 secs per iteration
--11g
1.17 secs
.00117 secs per iteration
7.46 secs
.00746 secs per iteration

Not only has it not performed better, its comparative performance has deteriorated as versions go up. So while we have removed duplicate code and enhanced the readability of our code, this example shows it's not necessarily more efficient.

It may just be a poor example to illustrate the performance benefit of this feature. It shows while a documented effect may be true in some circumstances, you must always test your particular scenario on your framework to get accurate comparisons. For further comment on this paradigm, I highly recommend reading this. Twice.