About Oracle WITH clause
About Oracle WITH clause
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”:
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”:
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”:
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/7728585/viewspace-1323302/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Model ClauseOracle
- Oracle SQL Model ClauseOracleSQL
- About Oracle LockOracle
- About Oracle HanganalyzeOracle
- About the Oracle GoldenGate TrailOracleGoAI
- about oracle10g racOracle
- About the Oracle Universal Installer Inventory!Oracle
- ANALYZE INDEX clauseIndex
- about oracle10g rac(轉)Oracle
- the program list about oracle database in SAP systemOracleDatabase
- An interesting thread on Oracle-l about BBEDRESTthreadOracle
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains 的問題 MySQLExpressAIMySql
- [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated cExpressAI
- ORA-01788: CONNECT BY clause required in this query block For ORACLE 10GUIBloCOracle 10g
- About HTMLHTML
- About interviewView
- About Personality
- About IndexDBIndex
- about bapiAPI
- ALTER SESSION ADVISE ClauseSession
- ALTER SYSTEM DISCONNECT SESSION ClauseSession
- oracle全文索引之About_INDEX_THEMES操作Oracle索引Index
- about datapump parallelParallel
- About post and get
- about histogram(2)Histogram
- about histogram(1)Histogram
- mysql主給備賦予許可權時報錯,MySQL [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clauseMySqlExpress
- An example about git hookGitHook
- All About PythonPython
- os staroge:about stripe
- error log about AIXErrorAI
- About SAP currency type
- about raw device[zt]dev
- UNRECOVERABLE DATAFILE clause in the ALTER DATABASE CLEAR LOGFILE statementDatabase
- 100 Things You Probably Didn't Know About Oracle DatabaseOracleDatabase
- 3.4.1 About Quiescing a DatabaseUIDatabase
- 2.3.3.1 About Application MaintenanceAPPAINaN
- 04 - More about CSS PositionCSS