with clause - 9i 기능.

with clause 를 이용하여 쿼리시 메모리에 쿼리결과를 저장하여 처리할 수 있도록 추가된 기능.
단, 9i JDBC Driver 에서는 안되는듯하다... (9i Driver 에서는 안되었습니다...)
"Subquery Factoring Clause" 라고도 한다.

Improving Query Performance with the SQL WITH Clause

Oracle9i significantly enhances both the functionality and performance of SQL to address the requirements of business intelligence queries. The SELECT statement’s WITH clause, introduced in Oracle9i, provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries. Consider a lengthy query which has multiple references to a single subquery block. Processing subquery blocks can be costly, so recomputing a block every time it is referenced in the SELECT statement is highly inefficient. The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times. The WITH clause, formally known as the subquery factoring clause, is part of the SQL-99 standard. The clause precedes the SELECT statement of a query and starts with the keyword “WITH.” The WITH is followed by the subquery definition and a label for the result set. The query below shows a basic example of the clause: WITH channel_summary AS   ( SELECT channels.channel_desc,        SUM(amount_sold) AS channel_total     FROM sales, channels     WHERE sales.channel_id = channels.channel_id     GROUP BY channels.channel_desc ) SELECT channel_desc, channel_total FROM channel_summary WHERE channel_total >   ( SELECT SUM(channel_total) * 1/3     FROM channel_summary ); This query uses the WITH clause to calculate the sum of sales for each sales channel and label the results as channel_summary. Then it checks each channel's sales total to see if any channel's sales are greater than one third of the total sales. By using the new clause, the channel_summary data is calculated just once, avoiding an extra scan through the large sales table. Although the primary purpose of the WITH clause is performance improvement, it also makes queries easier to read, write and maintain. Rather than duplicating a large block repeatedly through a SELECT statement, the block is localized at the very start of the query. Note that the clause can define multiple subquery blocks at the start of a SELECT statement: when several blocks are defined at the start, the query text is greatly simplified and its speed vastly improved. The SQL WITH clause in Oracle9i significantly improves performance for complex business intelligence queries. Together with the many other SQL enhancements in Oracle9i, the WITH clause extends Oracle's leadership in business intelligence. More Info Oracle9i SQL Reference: Chapter 17 - SELECT Statement Oracle9i Data Warehousing Guide: Chapter 18 - SQL for Aggregation in Data Warehouses Oracle9i Daily Features Archives

추가적 참고내용...

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.