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" <[EMAIL PROTECTED]>
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:[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: Tanel Poder
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).
