oracle CTE 简介
来源：互联网 发布：创新软件股票行情 编辑：程序博客网 时间：2024/06/11 08:54
oracle 9i r2中引入了SQL-99中的with子句，它是一个实现子查询的工具，我们可以用它来临时存储oracle经过计算得到的结果集。with子句的作用有点类似global temporary tables，设计with子句目的，是为提高复杂子查询的速度。下面是with子句的一些要点：1.with子句应用于oracle 9i以及更高版本2.正式的，with子句称作分解子查询3.with子句应用于当一个查询被多次执行的情况4.with子句对于递归查询也很有用(SQL-99,oracle不支持)为了简单起见，下面的例子仅仅引用集合一次；而with子句通常被用在一个查询中多次引用一个集合情况下。--with子句能够简化复杂的sql查询在SQL-99中，我们可以用with子句来代替临时表。oracle中的with子句一次性获得一个集合，并为其取一个名，以便于我们在后面的查询中引用到它。--首先，SQL-99中的with子句让人很困惑，因为这个sql语句不是以select开始的；然而，我们的查询语句可以以with子句开始，定义一个集合，在主查询中引用到集合，它就好像一个"真正的表"：with subquery_name as ( the aggregation SQL statement)select ( query naming subquery_name);--回到我们过于简单化的例子，我们用with子句代替临时表(注意：通过使用global temporary table,你会发现你的执行计划更优了，不过这取决于你的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_nameFROM store, sum_sales, number_stores, sales_by_storewhere store_sales > (all_sales / nbr_stores);--注释掉了oracle中没有公开的实现提示；oracle中的实现提示是用来确定在with子句内部创建的临时表是基于开销优化的。对于oracle 10g版本来说，这是不必要的，但是它确保了这个表只被创建一次。应该指出的是，with子句在oracle中并不是完全的起作用，oracle的sql中就不支持用with来替代connect by的递归查询。--Here is an actual performance comparison of equivalent queries:SQL> --*********************************************SQL> -- Using subqueriesSQL> --*********************************************SQL>SQL> select 2 store_name, 3 sum(quantity) store_sales, 4 (select sum(quantity) from sales)/(select count(*) from store) avg_sales 5 from 6 store s, 7 sales sl 8 where 9 s.store_key = sl.store_key 10 having 11 sum(quantity) > (select sum(quantity) from sales)/(select count(*) from store) 12 group by 13 store_name 14 ; ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 4 (25)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | | | 2 | TABLE ACCESS FULL | SALES | 100 | 400 | 2 (0)| 00:00:01 | | 3 | SORT AGGREGATE | | 1 | | | | | 4 | INDEX FULL SCAN | SYS_C003999 | 10 | | 1 (0)| 00:00:01 | |* 5 | FILTER | | | | | | | 6 | HASH GROUP BY | | 1 | 31 | 4 (25)| 00:00:01 | | 7 | NESTED LOOPS | | 100 | 3100 | 3 (0)| 00:00:01 | | 8 | TABLE ACCESS FULL | SALES | 100 | 900 | 2 (0)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID| STORE | 1 | 22 | 1 (0)| 00:00:01 | |* 10 | INDEX UNIQUE SCAN | SYS_C003999 | 1 | | 0 (0)| 00:00:01 | | 11 | SORT AGGREGATE | | 1 | 4 | | | | 12 | TABLE ACCESS FULL | SALES | 100 | 400 | 2 (0)| 00:00:01 | | 13 | SORT AGGREGATE | | 1 | | | | | 14 | INDEX FULL SCAN | SYS_C003999 | 10 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- 113 consistent gets //IO数量 -- SQL> --*********************************************SQL> -- Using CTAS(create table as select)SQL> --*********************************************SQL> create table t1 as select sum(quantity) all_sales from sales;Table created.SQL> create table t2 as select count(*) nbr_stores from store;Table created.SQL> create table t3 as select store_name, sum(quantity) store_sales from store natural join sales group by store_name;Table created.SQL>SQL> select 2 store_name 3 from 4 t1, 5 t2, 6 t3 7 where 8 store_sales > (all_sales / nbr_stores); ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 61 | 6 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 61 | 6 (0)| 00:00:01 | | 2 | MERGE JOIN CARTESIAN| | 1 | 26 | 4 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 | | 4 | BUFFER SORT | | 1 | 13 | 2 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | T2 | 1 | 13 | 2 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL | T3 | 1 | 35 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------ 30 consistent gets --SQL> --*********************************************SQL> -- Using the WITH clauseSQL> --*********************************************SQL>SQL> with 2 number_stores as 3 (select count(*) nbr_stores from store), 4 total_sales as 5 (select sum(quantity) all_sales from sales), 6 store_sales as 7 (select store_name, sum(quantity) sales from store natural join sales group by store_name) 8 select 9 store_name 10 from 11 number_stores, 12 total_sales, 13 store_sales 14 where 15 sales > (all_sales / nbr_stores); ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 61 | 7 (15)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 61 | 7 (15)| 00:00:01 | | 2 | NESTED LOOPS | | 1 | 26 | 3 (0)| 00:00:01 | | 3 | VIEW | | 1 | 13 | 1 (0)| 00:00:01 | | 4 | SORT AGGREGATE | | 1 | | | | | 5 | INDEX FULL SCAN | SYS_C003999 | 10 | | 1 (0)| 00:00:01 | | 6 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | 7 | SORT AGGREGATE | | 1 | 4 | | | | 8 | TABLE ACCESS FULL | SALES | 100 | 400 | 2 (0)| 00:00:01 | |* 9 | VIEW | | 1 | 35 | 4 (25)| 00:00:01 | | 10 | SORT GROUP BY | | 10 | 310 | 4 (25)| 00:00:01 | | 11 | NESTED LOOPS | | 100 | 3100 | 3 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | SALES | 100 | 900 | 2 (0)| 00:00:01 | | 13 | TABLE ACCESS BY INDEX ROWID| STORE | 1 | 22 | 1 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | SYS_C003999 | 1 | | 0 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- 109 consistent gets
原文：http://www.dba-oracle.com/t_with_clause.htm
- oracle CTE 简介
- oracle CTE
- sql CTE简介
- CTE~
- CTE
- CTE
- Oracle 11g中CTE应用示例
- Oracle 11g中CTE应用示例
- CTE在Oracle和Sqlserver中使用的差异
- With (CTE、递归CTE)
- Oracle---简介
- oracle简介
- oracle 简介
- Oracle简介
- Oracle简介
- Oracle-简介
- Oracle 简介
- Oracle简介
- c#代码的一些模式
- VB Inte控件 仿FlagshGet下载软件
- 黑暗中要坚持
- 快速排序算法(Quick Sort)(java)
- Get subtitle file for TED video
- oracle CTE 简介
- C++中extern “C”含义深层探索(转)
- 有理想的程序员必须知道的15件事
- 从今天开始
- Kmp算法
- 找到两个数组中的相同元素
- BizTalk 发布服务接口后访问该服务出现错误“Internal SOAP process failed”
- 俞敏洪在《赢在中国》中的经典点评语录
- 各大开源cms劲爆漏洞
