Oracle sql 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:
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:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12932950/viewspace-709699/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE UPDATE 語句語法與效能分析Oracle
- oracle sql語句OracleSQL
- Oracle基本SQL語句OracleSQL
- oracle常用SQL語句OracleSQL
- oracle的sql語句OracleSQL
- SQL查詢語句 (Oracle)SQLOracle
- Oracle SQL語句分類OracleSQL
- PL/SQL與DDL語句SQL
- Oracle SQL精妙SQL語句講解OracleSQL
- Java面試需要知道的SQL語句語法Java面試SQL
- GaussDB SQL基礎語法示例-迴圈語句SQL
- 必知必會——SQL語句基本語法整理SQL
- ORACLE START WITH 語句的樹級結構例子Oracle
- Oracle之sql語句優化OracleSQL優化
- Oracle維護常用SQL語句OracleSQL
- ORACLE很重要的sql語句OracleSQL
- 高效率Oracle SQL語句OracleSQL
- SQL語句規範的寫法SQL
- OCI插入SQL語句的寫法SQL
- 18 與Oracle Data Guard 相關的SQL語句OracleSQL
- SQL語句SQL
- Oracle SQL精妙SQL語句講解(轉)OracleSQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句?JavaSQL
- MySql與Sql Server Update語句MySqlServer
- ORACLE 資料庫 查詢語句與DML語句Oracle資料庫
- 6.4. PL/SQL語法——6.4.5. 迴圈語句SQL
- flask之控制語句 if 語句與for語句Flask
- oracle之PLSql語言(二)sql語句的使用OracleSQL
- [SQL] Oracle基礎語法SQLOracle
- Oracle常用sql語法集合OracleSQL
- 後臺執行SQL語句(oracle)SQLOracle
- 列出oracle dbtime得sql語句OracleSQL
- Oracle SQL語句優化之UNIONOracleSQL優化
- Oracle SQL語句執行步驟OracleSQL
- Oracle SQL select練習語句OracleSQL
- Oracle 行轉列的sql語句OracleSQL
- oracle、my sql、sql隨機查詢語句OracleSQL隨機
- SQL語句與正規表示式SQL