Oracle With syntax example

Keywords: Oracle

Oracle with statement example

WITH a AS (SELECT * FROM bd_member where rownum <10 ),
B AS (SELECT * FROM tp_trade_card)
Select A. MEMBER_NAME,
B. CARD_NO
From,
B
Where A. BD_MEMBER_ID = B. BD_MEMBER_ID

 

Google Search: Oracle + with view to get results...

 

 

Oracle with clause Oracle tips by Burleson Consulting

 

About Oracle with clause

Starting
In Oracle9i release 2 we see an incorporation of the SQL-99"
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
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
Aggregations once, where the SQL "with clause" is normally used when
Aggregation is referenced multiple times in a query.

We
Can also use the SQL-99 "WITH clause" instead of temporary tables.
Oracle SQL "WITH clause" will compute the aggregation once, give it
Name, and allow us to reference it (maybe multiple times), later in
Query.

The SQL-99"
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
Select/* + materialize */
Sum (quantity) all_sales from stores
Number_stores
Select/* + materialize */
Count (*) nbr_stores from stores
Sales_by_store
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 uninitialized ented "materialize" hint in the"
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 shoshould 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 faster Ming recursive queries.

To see how the "WITH clause" is used in ANSI SQL-99 syntax, here is
Excerpt from Jonathan Gennick's great work "Understanding the
Clause "showing the use of the SQL-99" WITH clause "to traverse
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
"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 ":/

 

 

_________________________________________________________________

URL: http://kapokfly.javaeye.com/blog/48406