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
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 SCANX_CLAIM_N11
Note the bitmap index
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
