oracle使用with语句,Oracle With 语句语法及示例

Starting in Oracle9i release 2 we see

an incorporation of the SQL-99 "WITH

clause", 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)

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.

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:

WITH

subquery_name

AS

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example,

let's replace the temporary tables with the

SQL "WITH  clause" (Note:  You may

find a faster execution plan by using Global

Temporary tables, depending on your release

of Oracle):

WITH sum_sales AS

( select /*+ materialize */  sum(quantity) all_sales from stores

),

number_stores AS

( select /*+ materialize */

count(*) nbr_stores from stores ),

sales_by_store AS

( select /*+ materialize */

store_name, sum(quantity) store_sales from

store natural join sales )

SELECT

store_name

FROM

store,

sum_sales,

number_stores,

sales_by_store

where

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

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:

WITH

subquery_name

AS

(the aggregation SQL statement)

SELECT

(query naming subquery_name);

Retuning to our oversimplified example,

let's replace the temporary tables with the

SQL "WITH" clause":

Link： http://www.dba-oracle.com/t_with_clause.htm

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

Link: http://www.oracle.com/technology/products/oracle9i/daily/oct10.html