Oracle-L: RE: Another UNION question

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Thu, 31 Jul 2003 17:44:23 -0800
Message-ID: <F001.005C8108.20030731174423@fatcity.com>

If the union performed index lookups, but the table example performed a full table scan it might. Of course with iterative index usage the point is deprecated. However before they were introduced the first query would perform an FTS even if emp_type was the primary key. Even with iterative index usage the union statement is often faster.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu

-----Original Message-----
Sent: Thursday, July 31, 2003 5:54 PM
To: Multiple recipients of list ORACLE-L

I'm getting back to work on my union article, and I have yet another union question. Are there ever cases where a UNION might be used for performance reasons? For example, I could write:

SELECT *
FROM emp
WHERE emp_type='HOURLY'

OR emp_type='CONTRACT';

or I could write:

SELECT *
FROM emp
WHERE emp_type='HOURLY'
UNION
SELECT *
FROM emp
WHERE emp_type='CONTRACT';

This is probably too simple of an example, but are there ever cases where using a UNION like this makes sense from a performance point-of-view?

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jonathan Gennick
INET: jonathan_at_gennick.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------