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 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
|
Link: http://www.oracle.com/technology/products/oracle9i/daily/oct10.html
以例子学习with：
1.with
2.--查询部门和部门的总薪水
3. dept_costs as (
4. select d.department_name,sum(e.salary) dept_total
5. from departments d,employees e
6. where d.department_id=e.department_id
7. group by d.department_name
8. ),
9.--利用上一个with查询的结果，计算部门的平均总薪水
10. avg_costs as (
11. select avg(dept_total) dept_avg
12. from dept_costs
13. )
14.--从两个with查询中比较并且输出查询结果
15. select *
16. from dept_costs
17. where dept_total > (select dept_avg from avg_costs)
18. order by department_name
注释：
① 子查询可重用相同或者前一个with查询块，通过select调用（with子句也只能被select调用）
② with子句的查询输出存储到用户临时表空间，一次查询，到处使用
③ 同级select前有多个查询定义，第一个用with，后面的不用with，并且用逗号分割
④ 最后一个with查询块与下面的select调用之间不能用逗号分割，只通过右括号分离，with子句的查询必须括号括起
⑤ 如果定义了with子句，而在查询中不使用，则会报ora-32035错误，只要后面有引用的即可，不一定在select调用，在后with查询块引用也是可以的
⑥ 前面的with子句定义的查询在后面的with子句中可以使用，但是一个with子句内部不能嵌套with子句
⑦ with查询的结果列有别名，引用时候必须使用别名或者*
再来看with的语法
㈠ as和select中的括号不能省略
㈡ 同级别select调用，with只能定义一次，多个用逗号分隔，但最后一个with子查询与下面的实际查询之间没有逗号
with子句的优点
① with子句有可能会改变执行计划
② with子查询只执行一次，将结果存储在用户的临时表空间，可多次引用，增加性能
③ sql的可读性较强
案例：
Ⅰ一般使用方式
1.with
2.--查询销售部门员工的姓名
3. saler_name as (
4. select department_id from departments where department_name='SALES' order by department_id
5. )
6.select last_name,first_name
7. from employees e
8. where department_id in (select * from saler_name) 注释：使用with子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用会报错。而且with子句获得的是一个临时表，必须采用select from （with查询名）
Ⅱ 在多数子查询中引用，同级可见
1.select last_name
2. from (with
3.--查询销售部门员工的姓名
4. saler_name as (
5. select department_id from departments where department_name='SALES' order by department_id
6. )
7.select last_name,first_name
8. from employees e
9. where department_id in (select * from saler_name)
10. )
Ⅲ 在集合在引用
集操作的两个select调用被当做是同级的，不能出现两个with定义
1.with
2.--查询销售部门员工的姓名
3. saler_name as (
4. select department_id from departments where department_name='SALES' order by department_id
5. )
6.select last_name,first_name
7. from employees e
8. where department_id in (select * from saler_name)
9.union all
10.select last_name,to_char(null)
11. from employees
以上部分文章来源于 Linux公社网站(www.linuxidc.com)
优化：ROWNUM的一个性能陷阱
http://www.hellodba.com/reader.php?ID=73&lang=CN
