Amit -- Thanks for the question regarding "Union queries", version 10g

You Asked

Hi,

I have one question...

As per my understanding Oracle process UNION queries in serial manner.

SELECT ....
FROM TAB1 -- FIrst query
UNION ALL
SELECT ....
FROM TAB2 -- Second query
UNION ALL
SELECT ....
FROM TAB3-- Third query

Oracle will first execute query 1 than query 2 and after that 3. So if each query is taking 5 seconds, we need to wait min 15 seconds for results. Is there any way we can run these queries simulataneously (in parallel manner) and combined result.

Thanks,
Amit

and we said...

Oracle is allowed to rewrite the queries in many ways

however, with a UNION such as:

(A) union (B) union (C)

you really have:

select distinct *
from ( (a) union all (b) union all (c) )


so, in general you have to wait for the last row to be produced to get the first row from a UNION because of the implied distinct.

So, the question to you is: is UNION what you really meant, or is the more efficient UNION ALL - which can start returning data IMMEDIATELY - what you want.

Reviews

Union queries   February 22, 2007 - 12pm Central time zone Bookmark | Bottom | Top Reviewer: Amit Gupta from USA I am using UNION ALL. but my issue is... i have to group union all data and need to produce results. example: SELECT <col m...n> FROM ((A) union ALL (B) union ALL (C)) GROUP By <col list> so here grouping needs to wait result execution of each query in union all. And each query in union all will be executed in serial fashion so my question was ... Is there any way to make inner queries parallel ... Parallel Read   February 22, 2007 - 4pm Central time zone Bookmark | Bottom | Top Reviewer: Tamil from Atlanta USA Oracle will not parallelise the UNION ALL Query. Each "select" would be executed one after another. See the raw trace file below: PARSING IN CURSOR #1 len=71 dep=0 uid=65 oct=3 lid=65 tim=6223660255710 hv=1331560204 ad='3c859ba0' select * from t1 union all select * from t2 union all select * from t3 END OF STMT PARSE #1:c=570000,e=556014,p=22,cr=453,cu=0,mis=1,r=0,dep=0,og=1,tim=6223660255685 EXEC #1:c=0,e=164,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6223660256535 WAIT #1: nam='SQL*Net message to client' ela= 10 driver id=1650815232 ytes=1 p3=0 obj#=10221 tim=6223660256918 WAIT #1: nam='db file sequential read' ela= 93 file#=9 block#=167710 blocks=1 obj#=64888 tim=6223660258704 WAIT #1: nam='db file sequential read' ela= 95 file#=9 block#=167711 blocks=1 obj#=64888 tim=6223660260038 FETCH #1:c=0,e=3152,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=6223660260296 WAIT #1: nam='SQL*Net message from client' ela= 1175 driver id=1650815232 ytes=1 p3=0 obj#=64888 tim=6223660261963 WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1650815232 ytes=1 p3=0 obj#=64888 tim=6223660262278 WAIT #1: nam='db file sequential read' ela= 94 file#=9 block#=168990 blocks=1 obj#=64889 tim=6223660262684 WAIT #1: nam='db file scattered read' ela= 685 file#=9 block#=168991 blocks=10 obj#=64889 tim=6223660264706 FETCH #1:c=10000,e=3201,p=11,cr=4,cu=0,mis=0,r=15,dep=0,og=1,tim=6223660265387 WAIT #1: nam='SQL*Net message from client' ela= 2819 driver id=1650815232 ytes=1 p3=0 obj#=64889 tim=6223660268448 WAIT #1: nam='SQL*Net message to client' ela= 4 driver id=1650815232 ytes=1 p3=0 obj#=64889 tim=6223660268743 WAIT #1: nam='db file sequential read' ela= 94 file#=9 block#=170270 blocks=1 obj#=64890 tim=6223660269108 WAIT #1: nam='db file scattered read' ela= 502 file#=9 block#=170271 blocks=10 obj#=64890 tim=6223660270636 FETCH #1:c=0,e=2510,p=11,cr=4,cu=0,mis=0,r=14,dep=0,og=1,tim=6223660271175 WAIT #1: nam='SQL*Net message from client' ela= 3958 driver id=1650815232 ytes=1 p3=0 obj#=64890 tim=6223660275446 STAT #1 id=1 cnt=30 pid=0 pos=1 obj=0 op='UNION-ALL (cr=11 pr=24 pw=0 time=3111 us)' STAT #1 id=2 cnt=10 pid=1 pos=1 obj=64888 op='TABLE ACCESS FULL T1 (cr=4 pr=2 pw=0 time=3077 us)' STAT #1 id=3 cnt=10 pid=1 pos=2 obj=64889 op='TABLE ACCESS FULL T2 (cr=4 pr=11 pw=0 time=2833 us)' STAT #1 id=4 cnt=10 pid=1 pos=3 obj=64890 op='TABLE ACCESS FULL T3 (cr=3 pr=11 pw=0 time=2126 us)' WAIT #0: nam='SQL*Net message to client' ela= 6 driver id=1650815232 ytes=1 p3=0 obj#=64890 tim=6223660276420 WAIT #0: nam='SQL*Net message from client' ela= 7122097 driver id=1650815232 ytes=1 p3=0 obj#=64890 tim=6223667398685 ===================== Union Queries   February 28, 2007 - 2pm Central time zone Bookmark | Bottom | Top Reviewer: Amit Gupta from USA Why oracle is not providing parallel execution for diffrent UNION ALL SQL blocks? Is there any specific reason for it. Followup   February 28, 2007 - 3pm Central time zone: each individual query can be done in parallel within the union all - that is sort of what you want - "get me first query fast, then second query fast" not "get me something from someone slow" in general. but union and union all operations can be parallelized http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#sthref1999

---

Write a Review