With OR expansion, the optimizer transforms a query with an OR condition in the predicate into two select statements that are combined with a union all.
create table tq84_tab (
id number ,
txt varchar2(20),
num number,
val varchar2(20)
--
-- constraint tq84_tab_pk primary key(id)
) ;
create index tq84_tab_ix_txt on tq84_tab(txt);
create index tq84_tab_ix_num on tq84_tab(num);
explain plan for
select
val
from
tq84_tab
where
num = 42 or
txt = 'xyz'
;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- -----------------------------------------------------------------
-- | Id | Operation | Name |
-- -----------------------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | VIEW | VW_ORE_8E03FE63 |
-- | 2 | UNION-ALL | |
-- | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_TAB |
-- | 4 | INDEX RANGE SCAN | TQ84_TAB_IX_NUM |
-- | 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_TAB |
-- | 6 | INDEX RANGE SCAN SCAN | TQ84_TAB_IX_TXT |
-- -----------------------------------------------------------------
drop table tq84_tab;