-
Best Substitute For Union ALL
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
-
Re: Best Substitute For Union ALL
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
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?
-
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:
Code:
SELECT
COL1 ,
COUNT(*)
FROM
TABLE1
WHERE
COL1 = 5
UNION ALL
SELECT
COL1 ,
COUNT(*)
FROM
TABLE1
WHERE
COL1 = 4
...then it should obviously be collapsed to:
Code:
SELECT
COL1 ,
COUNT(*)
FROM
TABLE1
WHERE
COL1 IN (4,5)
GROUP BY
COL1
If, however, you are UNIONing SELECts from 2 completely un-related tables, then the UNION ALL wins.
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|