Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







SQL WITH clause tricks

Oracle SQL Tips by Donald BurlesonFebruary 23, 2015

Starting in Oracle9i release 2 we saw an incorporation of the SQL-99 WITH clause (a.k.a. subquery factoring), a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

The SQL WITH clause is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle WITH clause:

   - The SQL WITH clause only works on Oracle 9i release 2 and beyond.
   - Formally, the WITH clause is called subquery factoring
   - The SQL WITH clause is used when a subquery is executed multiple times
   - Also useful for recursive queries (SQL-99, but not Oracle SQL)

Let's take a closer look at how the Oracle SQL WITH clause works:

All Stores with above-average sales

To keep it simple, the following example only references the aggregations once, where the SQL WITH clause is normally used when an aggregation is referenced multiple times in a query.

Here is an example of a request to see the names of all stores with above-average sales. For each store, we must compare their average sales to the average sales for all stores.
Essentially, this query accesses the STORE and SALES tables, comparing the sales for each store with the average sales for all stores. To answer this query we must know:

? The total sales for all stores.
? The number of stores.
? The sum of sales for each store.

To answer this in a single SQL statement we need to employ in-line views and also a subquery inside a HAVING clause:

sum(qty) store_sales,
(select sum(qty) from sales)/(select count(*) from stores) avg_sales
stores s,
sales sl
s.stor_id = sl.stor_id
sum(qty) > (select sum(qty) from sales)/(select count(*) from stores)
group by

While this query provides the correct answer, it is difficult to read and complex to execute, re-computing the sum of sales multiple times. 
To prevent the unnecessary re-execution of the aggregation (sum(sales)), we could create temporary tables and use them to simplify our query. 

1 - Create a table t1 to hold the total sales for all stores.
2 - Create a table t2 to hold the number of stores. 
3 - Create a table t3 to hold the store name and the sum of sales for each store.

Then, write a fourth SQL statement that uses tables T1, T2, and T3 to replicate the output from the original query.  The final answer will look like this: 

create table t1 as
select sum(quantity) all_sales from stores;

create table t2 as
select count(*) nbr_stores from stores;
create table t3 as
select store_name, sum(quantity) store_sales from store natural join sales;
   store_sales > (all_sales / nbr_stores);

While this is a very elegant solution (i.e. easy to understand) and has faster execution time, we can also use the SQL-99 WITH clause instead of temporary tables.  The Oracle SQL WITH clause will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

The SQL-99 WITH clause is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the WITH clause to start our SQL query, defining the aggregations, which can then be named in the main query as if they were ?real? tables:

  (the aggregation SQL statement)
  (query naming subquery_name);

Retuning to our oversimplified example, let's replace the temporary tables with the SQL ?WITH? clause?:

   sum_sales AS
      ( select
      sum(qty) all_sales from sales ),
   number_stores AS
      ( select
      count(*) nbr_stores from stores ),
   sales_by_store AS
      ( select
      stor_name, sum(qty) store_sales from
      stores natural join sales
      group by stor_name)
   store_sales > (all_sales / nbr_stores);

Note the use of the Oracle undocumented ?materialize? hint in the WITH clause.  The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the WITH clause.  This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

It should be noted that the WITH clause does not yet fully-functional within Oracle SQL and it does not yet support the use of WITH clause replacement for CONNECT BY when performing recursive queries.

To see how the WITH clause is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick's great work ?Understanding the WITH Clause? showing the use of the SQL-99 WITH clause to traverse a recursive bill-of-materials hierarchy. 

NOTE: This does NOT work (yet) with Oracle SQL

WITH recursiveBOM
   (assembly_id, assembly_name, parent_assembly) AS
(SELECT parent.assembly_id,
FROM bill_of_materials parent
WHERE parent.assembly_id=100
SELECT child.assembly_id,
FROM recursiveBOM parent, bill_of_materials child
WHERE child.parent_assembly = parent.assembly_id)
SELECT assembly_id, parent_assembly, assembly_name
FROM recursiveBOM;
Reader comments:
From an article on your site I started to use the Oracle With syntax. Not only now I can write clearer SQL syntax I can also materialize the inline views to make large statements perform well.
Gerrit-Jan Linker

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster