DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Best Substitute For Union ALL

  1. #1
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    38

    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

  2. #2
    Join Date
    Mar 2002
    Posts
    200

    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?

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width