Re: Another UNION question

Hi!

AFAIK, Rule Based Optimizer always converts ORs to Union alls (except when doing an outer join or connect by query). That's called OR expansion. CBO seems to prefer inlist iterators:

SQL> create table t as select * from sys.obj$;

Table created.

SQL> select * from t where obj# = 1 or obj# = 2 or obj# = 3 or obj# = 4 or obj# = 5 or obj# = 6 or obj# = 7;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'T' SQL> create index i on t(obj#);

Index created.

SQL> select * from t where obj# = 1 or obj# = 2 or obj# = 3 or obj# = 4 or obj# = 5 or obj# = 6 or obj# = 7;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 CONCATENATION

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   3    2       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   5    4       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   7    6       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   9    8       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
  10    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
  11   10       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
  12    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
  13   12       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)
  14    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
  15   14       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE)

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select * from t where obj# = 1 or obj# = 2 or obj# = 3 or obj# = 4 or obj# = 5 or obj# = 6 or obj# = 7;

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=7 Bytes=581)    1 0 INLIST ITERATOR

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=7 Byte
          s=581)

   3    2       INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=2 Card=7)

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, August 01, 2003 3:54 AM

> 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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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