Printable View
Hi All,
Funny It may seem cos I am asking this without any referance...
Whenever you look at a querry and see a lots of UNION ALL in them..
What is the other option that that should come into your mind immediately...Cos I believe UNION ALL takes lots of time to execute..
Thanks in advance,
Paddy
Not sure if am 100% correct, but I think Set Operations in Oracle do not use indexes by default. May be that's the cause for the exhorbitant time consumed. Why not use joins between the tables if they are constraint-related?Quote:
Originally posted by paddy04
Hi All,
Funny It may seem cos I am asking this without any referance...
Whenever you look at a querry and see a lots of UNION ALL in them..
What is the other option that that should come into your mind immediately...Cos I believe UNION ALL takes lots of time to execute..
Thanks in advance,
Paddy
Have to disagree there ... UNION ALL is not an inherently slow operation. Unlike UNION there is no SORT to take place, and each of the sets returns it's rows as soon as they have been identified.
Other than multiple queries, there is no substitute that leaps to mind, and I'd bet that multiple queries would be slower.
It depends very much on what the individual SELECT statements are that are being UNIONed. If, for example, one has:
...then it should obviously be collapsed to:Code:SELECT
COL1 ,
COUNT(*)
FROM
TABLE1
WHERE
COL1 = 5
UNION ALL
SELECT
COL1 ,
COUNT(*)
FROM
TABLE1
WHERE
COL1 = 4
If, however, you are UNIONing SELECts from 2 completely un-related tables, then the UNION ALL wins.Code:SELECT
COL1 ,
COUNT(*)
FROM
TABLE1
WHERE
COL1 IN (4,5)
GROUP BY
COL1
This can be abstracted to a larger rule: Always factor out similar tables from a UNION. In set notation, (ac)U(bc) => c(aUb). This will allow the table c to be accessed only once instead of twice. Of course, this should only be applied where c is a lookup (non-restrictive) table.
- Chris