RE: Another UNION question

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.
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On 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: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: Larry Elkins
  INET: elkinsl_at_flash.net

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).