·
The Oracle WITH clause is an incredibly powerful tool available since Oracle9i R2 that enables the user to create virtual views that become reusable via an alias throughout the main body of a query. A WITH clause (officially called a Subquery Factoring Clause) is pre-pended to a SELECT statement. The power of the WITH clause becomes evident in situations where a subquery, or indeed any portion of a query, is used in more than one location throughout a SELECT statement. Since the results from a WITH clause are calculated only once, dramatic performance improvements may be realized. As an added benefit, WITH clauses tend to simplify the look of a query since repeated sections are centralized and aliased. The basic syntax for a SELECT statement using a WITH clause may look a little strange at first and some tools (i.e. Oracle Reports in some cases) may be unable to properly parse this construct:
WITH alias_name -- Alias to use in the main query AS (insert a query here) SELECT... -- Beginning of the query main body
It should be noted that multiple aliases can be defined in the WITH clause:
WITH alias_name1 AS (query1) aleas_name2 AS (query2) SELECT...
As previously indicated, the intended use of a WITH clause is to increase efficiency by eliminating repeated query sections in a SELECT statement. The following query (which we realize could be written in a more efficient manner!), returns customers from a fictitious SALES table that were found in the top ten for both January and February. This query is constructed using two in-line views, one for the January calculation and one for February. Notice the duplication in the in-line views aliased as “jan” and “feb”; only the date ranges are different:
For this exercise, we employ the simple table Sales
with three columns:
customer_name VARCHAR2;
sales_date DATE;
customer_sales NUMBER;
SELECT top_10_jan.customer_name,
top_10_jan.total_sales_jan,
top_10_feb.total_sales_feb
FROM (
SELECT customer_name,
total_sales_jan
FROM (
SELECT customer_name,
SUM(customer_sales) total_sales_jan
FROM sales
WHERE sales_date
BETWEEN '01-jan-06' AND '31-JAN-06'
GROUP BY customer_name
ORDER BY 2 DESC
) jan
WHERE rownum <11
) top_10_jan,
(
SELECT customer_name,
total_sales_feb
FROM (
SELECT customer_name,
SUM(customer_sales) total_sales_feb
FROM sales
WHERE sales_date
BETWEEN '01-FEB-06' AND '28-FEB-06'
GROUP BY customer_name
ORDER BY 2 DESC
) feb
WHERE rownum <11
) top_10_feb
WHERE top_10_jan.customer_name = top_10_feb.customer_name;
By substituting WITH clause in place of the two in-line views, the above query can be rewritten in a more efficient manner:
WITH
tot_sales
AS
(
SELECT customer_name,
sales_date,
total_sales,
RANK() OVER (PARTITION BY sales_date
ORDER BY total_sales DESC) month_rank
FROM (
SELECT customer_name,
TRUNC(sales_date,'MONTH') sales_date,
SUM(customer_sales) total_sales
FROM sales
WHERE sales_date >= '01-JAN-06'
AND sales_date <= '28-FEB-06'
GROUP BY customer_name,
TRUNC(sales_date,'MONTH')
) jan
)
SELECT tot_jan.customer_name,
tot_jan.total_sales,
tot_feb.total_sales
FROM tot_sales tot_jan,
tot_sales tot_feb
WHERE tot_jan.sales_date = '01-JAN-06'
AND tot_feb.sales_date = '01-FEB-06'
AND tot_jan.month_rank <11
AND tot_feb.month_rank <11
AND tot_jan.customer_name = tot_feb.customer_name;
Just how much more efficient is our new query? Explain plans for each query using Oracle 10G R2 and a table with 25000 rows yields the following query costs:
Query method 1: In-line views
Query Method 2: WITH Clause
The query using in-line views shows a cost of 1449, but re-writing the query to use a WITH clause shows a drop to 861! This big savings comes from the elimination of a second full table scan of the SALES table. A WITH clause calculates its result set only once and re-uses it throughout the query. In our simulations, this query actually resulted in a 40% improvement in execution time. Not a bad little tuning trick!
We realize of course that this query could have been written without the need for in-line views OR the WITH clause and perhaps even more efficiently. Take for example the following query, which has a cost of 727 (but in practice was actually slower than the WITH query in terms of execution time):
SELECT customer_name,
jan_sales,
feb_sales
FROM
(
SELECT customer_name,
jan_sales,
feb_sales,
RANK() OVER (ORDER BY jan_sales DESC) jan_rank,
RANK() OVER (ORDER BY feb_sales DESC) feb_rank
FROM
(
SELECT customer_name,
SUM(DECODE(TRUNC(sales_date,'MONTH'),
'01-JAN-06', customer_sales,0)) jan_sales,
SUM(DECODE(TRUNC(sales_date,'MONTH'),
'01-FEB-06', customer_sales,0)) feb_sales
FROM sales
WHERE sales_date >= '01-JAN-06'
AND sales_date <= '28-FEB-06'
GROUP BY customer_name
)
)
WHERE jan_rank <11
AND feb_rank <11;
Clearly, there are many ways to solve a problem, and Subquerey Factoring adds another powerful tool to facilitate writing both efficient and elegant queries. Use it, but remember that the WITH clause comes with limitations and sometimes returns unexpected results. But more on that in a different post.
