I've used a UNION where I wanted MIN and MAX from an indexed column:
select max(dspnd_date), min(dspnd_date)
from dwcorp.t_claim partition (p_200206)
SELECT STATEMENT Hint=CHOOSE
SORT AGGREGATE
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX FULL SCAN X_CLAIM_N11
Note the bitmap index full scan in the above query, it took 13 seconds,
around 35 million rows in the partition and 30 distinct values and an even
distribution on dpsnd_date. Compare with the bitmap index single value
approach below:
select max(dspnd_date)
from dwcorp.t_claim partition (p_200206)
UNION ALL
select min(dspnd_date)
from dwcorp.t_claim partition (p_200206)
SELECT STATEMENT Hint=CHOOSE
SORT UNIQUE
UNION-ALL
SORT AGGREGATE
BITMAP INDEX SINGLE VALUE X_CLAIM_N11
SORT AGGREGATE
BITMAP INDEX SINGLE VALUE X_CLAIM_N11
This ran in 335 ms (as opposed to 13 seconds). And of course I could wrapper
the second one in an inline view and use a decode or case trickery to bring
onto a single line. And I know there are other cases, but I can't think of
them off the top of my head. But the fact that the CBO will sometimes expand
an OR into multiple UNIONED statements makes you think there are cases.
The above was with a BMI. On 8.1.7 and a b-tree, you will see something like
INDEX FULL SCAN (MIN/MAX) which really seems to operate using an asc or desc
index scan and a stop key when using the UNION approach or an index (fast)
full scan when doing both min and max in one statement. Once again the UNION
ALL did it faster.
Regards,
Larry G. Elkins
The Elkins Organization Inc.
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
> Jonathan Gennick
> Sent: Thursday, July 31, 2003 7:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Another UNION question
>
>
> 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:[EMAIL PROTECTED]
>
> 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Larry Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
