oracle的with函式用法示例
with a as (select dummy from dual),
b as (select dummy from dual)
select a.dummy,b.dummy from a,b
where a.dummy = b.dummy
Retuning to our oversimplified example, let’s replace the temporary tables with the SQL “WITH” clause”:[@more@]
b as (select dummy from dual)
select a.dummy,b.dummy from a,b
where a.dummy = b.dummy
/********************************/
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.
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”:
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”:
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”:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/18921899/viewspace-1016957/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- openat()函式的用法示例函式
- PHP trim()函式 用法示例PHP函式
- Sanic response stream() 函式用法和示例函式
- Sanic response redirect() 函式用法和示例函式
- Sanic response raw() 函式用法和示例函式
- Sanic response file() 函式用法和示例函式
- Sanic response html() 函式用法和示例HTML函式
- Sanic response text() 函式用法和示例函式
- 【Oracle的NVL函式用法】Oracle函式
- Oracle dump函式的用法Oracle函式
- Oracle trunc()函式的用法Oracle函式
- Oracle 函式 Translate 的用法Oracle函式
- oracle table()函式用法Oracle函式
- Sanic response json() 函式用法和示例JSON函式
- Oracle to_date()函式的用法Oracle函式
- 【轉】oracle的substr函式的用法Oracle函式
- Sanic response file_stream() 函式用法和示例函式
- Python常見工廠函式用法示例Python函式
- ORACLE單行函式與多行函式之二:字元函式示例Oracle函式字元
- ORACLE單行函式與多行函式之四:日期函式示例Oracle函式
- ORACLE單行函式與多行函式之六:通用函式示例Oracle函式
- oracle資料庫常用分析函式與聚合函式的用法Oracle資料庫函式
- ORACLE單行函式與多行函式之五:轉換函式示例Oracle函式
- ORACLE單行函式與多行函式之七:多行函式之分組函式示例Oracle函式
- Oracle - DBMS_LOB函式和用法Oracle函式
- pipe row的用法, Oracle split 函式寫法.Oracle函式
- oracle中函式to_char()的用法介紹Oracle函式
- ascii函式和substr函式的用法ASCII函式
- GetModuleFileName函式的用法函式
- createStyleSheet()函式的用法函式
- qsort函式的用法函式
- COALESCE函式的用法。函式
- Oracle 中的 ROW_NUMBER() OVER() 分析函式的用法Oracle函式
- Instr函式的用法函式
- fork()函式的基本用法函式
- Translate函式用法函式
- abs函式用法函式
- jQuery函式的等價原生函式程式碼示例jQuery函式