Re: Multiple selects in one
Date: Sat, 13 Mar 2010 03:41:59 -0800 (PST)
On Mar 12, 5:29 pm, The Magnet <a..._at_unsu.com> wrote:
> > Interesting query. It works. I never really understood the 'WITH' > queries. Reading on it did not really help either.
As mentioned by others, the WITH clause is only used here to generate some test data, it has nothing to do with the PIVOT operation performed.
In general understanding the WITH clause (also called "Subquery factoring" or "Common Table Expression (CTE)") is not complicated - it is just another way of writing an inline view, so you could rewrite my sample query without changing its meaning using an inline view like that:
FROM ( SELECT customer_id , max(decode(product_id, 204, null, subscription_id)) as non_ut_subscr_id , max(decode(product_id, 204, null, product_id)) as non_ut_product_id , max(decode(product_id, 204, null, status)) as non_ut_status , max(decode(product_id, 204, subscription_id, null)) as ut_subscr_id , max(decode(product_id, 204, product_id, null)) as ut_product_id , max(decode(product_id, 204, status, null)) as ut_status FROM ( select 12345 as CUSTOMER_ID , 203 as PRODUCT_ID , 6767 as SUBSCRIPTION_ID , 2 as STATUS from dual union all select 12345 as CUSTOMER_ID , 204 as PRODUCT_ID , 6768 as SUBSCRIPTION_ID , 1 as STATUS from dual ) subscriptions WHERE customer_id = 12345 GROUP BY customer_id )
The added value of the WITH clause is many-fold:
- A query might look more understandable when separating out complex inline views
- It is best suited for a "peel the onion" or "divide-and-conquer" approaches where each subquery is based on one or more of the previous ones, and is in particular helpful if the same view is used more than once. You'll agree that the following query needs to include the view "a" only once and is better readable:
with a as (
b as (
x != y
c as (
where a.x > b.y
than this one, which has the same meaning:
( select ... from ( select ... from ( select x , y from tab_z ) a where x != y ) b , ( select x , y from tab_z ) a where a.x > b.y ) c;
Notice in particular that I had to write the view "a" twice when using regular inline views.
3. Not only it looks nicer, but Oracle is going to attempt to materialize (by creating a system generated global temporary table on the fly) the view results if the view is used more than once, so in my example above the view "a" would get instantiated as a global temporary table and Oracle needs to execute the query of the inline view only once, rather than twice - of course there is an overhead of writing the and re-reading the global temporary table.
4. The SQL standard defines recursive WITH clauses that have been supported by other vendors and now is also supported by Oracle 11.2 (with some limitations). In other database systems the recursive WITH clause could be used to serve similar purposes as the hierarchical query in Oracle (CONNECT BY ... START WITH) , but it is not limited to that.
Oracle related stuff blog:
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sat Mar 13 2010 - 05:41:59 CST