머큐리꿈

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;

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.