| 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. |
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...
|
오라클 9i 에서 제공하는 with 절은 여러개의 sub_query가 하나의 main-query에서 사용될 때 생기는 복잡성을 간결하게 정의 하여 발생할 수 있는 성능 저하 현상을 방지할 수 있다.
참고 할 만한 자료 : Understanding the WITH Clause -
http://gennick.com/with.html
참고 자료 : 이한출판사 - 오라클 ACE가 해설하는 NEW SQL & PL/SQL (저자 주종면) ,
http://www.oracle.com/technology/products/oracle9i/daily/oct10.html