Oracle With 语句语法及示例.doc

Oracle With 语句语法及示例1、一个完整的Oracle With 语句实例：insert into sms_tmp_stop_circlenum_zsdx 将下面查询的结果插入到临时表中WITH selectdata - Oracle With开始的查询语句AS(SELECT TRUNC(createtime) senddate,agentid,srcnum, ROUND(SUM(CASE WHEN result=4 THEN 1 ELSE 0 END)/COUNT(*)*100,2) AS ratio FROM ZSDX_SMS_OTHERSEND_DETAILS WHERE agentid LIKE zsdx% AND createtime=TO_DATE(2010-12-30 00:00:00,yyyy-mm-dd hh24:mi:ss)AND createtime=100) )a1,(SELECT srcnum,ratio,senddate FROM selectdata WHERE ratio=100) a2WHERE a1.srcnum=a2.srcnum2、详解：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_nameAS(the aggregation SQL statement)SELECT(query naming subquery_name);Retuning to our oversimplified example, lets 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_nameFROM store, sum_sales, number_stores, sales_by_storewhere 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 Gennicks great work Understanding the WITH Clause showing the use of the SQL-99 WITH clause to traverse a recursive bill-of-materials hierarchyThe 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_nameAS(the aggregation SQL statement)SELECT(query naming subquery_name);Retuning to our oversimplified example, lets replace the temporary tables with the SQL WITH clause:Link： /t_with_clause.htmImproving 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 statements 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_totalFROM channel_summaryWHERE 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 channels sales total to see if any channels 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