Hi Tom
I have an issue with OR expansion. Using literals, the OR conditions are expanded neatly.
However, I am unable to make the optimizer come up with a plan that uses the index, when I use a subquery.
drop table t1;
drop table t2;
create table t1 as
select object_id id1, data_object_id id2
from all_objects where object_id is not null;
alter table t1 add constraint t1_pk primary key (id1);
create index t1_i1 on t1 (id2);
create table t2 as
select id1, id2
from t1;
create index t2_i1 on t2 (id2);
select *
from t1
where id1 in ( 1, 2, 3)
or id2 in ( 4, 5 );
select * from table(dbms_xplan.display_cursor);
SQL_ID 3nrmyb6ku7cqa, child number 0
-------------------------------------
select * from t1 where id1 in ( 1, 2, 3) or id2 in ( 4, 5 )
Plan hash value: 3986143333
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | CONCATENATION | | | | | |
| 2 | INLIST ITERATOR | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_I1 | 41 | | 2 (0)| 00:00:01 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| T1 | 819 | 21294 | 5 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | T1_PK | 41 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(("ID2"=4 OR "ID2"=5))
6 - filter((LNNVL("ID2"=4) AND LNNVL("ID2"=5)))
7 - access(("ID1"=1 OR "ID1"=2 OR "ID1"=3))
Note
-----
- dynamic sampling used for this statement (level=2)
select *
from t1
where id1 in (select id1 from t2 where id2 = 3)
or id2 in ( 2, 3 );
select * from table(dbms_xplan.display_cursor);
SQL_ID 316pgp8n8jmup, child number 0
-------------------------------------
select * from t1 where id1 in (select id1 from t2 where id2 = 3) or
id2 in ( 2, 3 )
Plan hash value: 3689097399
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 37 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 83567 | 2121K| 37 (3)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_I1 | 318 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((INTERNAL_FUNCTION("ID2") OR IS NOT NULL))
3 - filter("ID1"=:B1)
4 - access("ID2"=3)
Note
-----
- dynamic sampling used for this statement (level=2)
with x as ( select /*+ mateialize */ id1 from t2 where id2 = 3 )
select *
from t1
where id1 in ( select id1 from x )
or id2 in ( 2, 3 );
select * from table(dbms_xplan.display_cursor);
SQL_ID 0fwy3zm22fmuy, child number 0
-------------------------------------
with x as ( select /*+ mateialize */ id1 from t2 where id2 = 3 ) select
* from t1 where id1 in ( select id1 from x ) or id2 in ( 2, 3 )
Plan hash value: 3689097399
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 37 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 83567 | 2121K| 37 (3)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_I1 | 318 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((INTERNAL_FUNCTION("ID2") OR IS NOT NULL))
3 - filter("ID1"=:B1)
4 - access("ID2"=3)
Note
-----
- dynamic sampling used for this statement (level=2)
Why is the optimizer insisting on full table scanning T1 ?
Br,
Kim
In Oracle Database 11g Release 2 OR-Expansion is a heuristic base query transformation and we don’t consider statements with subqueries for OR-Expansion since we can not unnest the subquery that would end up in one of the branching.
Starting in Oracle Database 12c R2, OR-Expansion is a cost-based transformation, which means both OR-Expansion and subquery unnesting could take place in the same query. Making it possible to be used in your example.
More information on the cost-based OR expansion transformation can be found in the "What to expect from the Optimizer when upgrading from 11g to 12c" whitepaper,
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf Without OR-Expansion, the Optimizer selects a full table scan of T1 in your second and third query, because of the cost. An index scan of T1_PK followed by a table access by ROWID is a lot more expensive.
You can see the costs if you generate a 10053 trace or if you force the index plan with a hint.
select /*+ index(t1) */ *
2 from t1
3 where id1 in (select id1 from t2 where id2 = 3)
4 or id2 in ( 2, 3 );
select * from table(dbms_xplan.display_cursor);
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 49 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 8192 | 208K| 49 (7)| 00:00:01 |
| 3 | INDEX FULL SCAN | T1_PK | 8192 | | 20 (5)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 26 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_I1 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
==== Abstract from 10053 Trace File ============================
Table: T1 Alias: T1
Card: Original: 8193.000000 Rounded: 8193 Computed: 8193.000000 Non Adjusted: 8193.000000
Scan IO Cost (Disk) = 7.000000
Scan CPU Cost (Disk) = 1542360.240000
Cost of predicates:
io = 0.000000, cpu = 69.800000, sel = 0.059500 flag = 2048 (OR chain)
io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2049 (Inlist)
io = NOCOST, cpu = NOCOST, sel = NOSEL flag = 0 ("T1"."ID2"=2)
io = NOCOST, cpu = NOCOST, sel = NOSEL flag = 0 ("T1"."ID2"=3)
io = NOCOST, cpu = 20.000000, sel = 0.050000 flag = 2562 ( EXISTS (SELECT 0 FROM "T2" "T2" WHERE "T2"."ID2"=3 AND "T2"."ID1"=:B1))
Total Scan IO Cost = 7.000000 (scan (Disk))
+ 0.000000 (io filter eval) (= 0.000000 (per row) * 8193.000000 (#rows))
= 7.000000
Total Scan CPU Cost = 1542360.240000 (scan (Disk))
+ 571871.400000 (cpu filter eval) (= 69.800000 (per row) * 8193.000000 (#rows))
= 2114231.640000
Access Path: TableScan
Cost: 8.761860 Resp: 8.761860 Degree: 0
Cost_io: 7.000000 Cost_cpu: 2114232
Resp_io: 7.000000 Resp_cpu: 2114232
****** trying bitmap/domain indexes ******
****** Costing Index T1_PK
Access Path: index (FullScan)
Index: T1_PK
resc_io: 19.000000 resc_cpu: 1773907
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 20.478256 Resp: 20.478256 Degree: 0
****** finished trying bitmap/domain indexes ******
Best:: AccessPath: TableScan
Cost: 8.761860 Degree: 1 Resp: 8.761860 Card: 8193.000000 Bytes: 0.000000