In OR expansion, the optimizer
transforms a query with a WHERE clause containing OR operators into a query that uses the UNION ALL operator. The database can
perform OR expansion for various reasons.
rajesh@ORA12C> create table t as
2 select *
3 fromall_objects;
Table created.
rajesh@ORA12C>
rajesh@ORA12C> create index t_idx1 on t(object_id);
Index created.
rajesh@ORA12C> create index t_idx2 on t(data_object_id);
Index created.
rajesh@ORA12C> create table t1 as
2 select * from (select *
3 from
all_objects
4 order by object_id)
5 where rownum<= 10;
Table created.
rajesh@ORA12C> set autotracetraceonly explain
rajesh@ORA12C> select * from t where data_object_id = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 369893979
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 115 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
1 | 115 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX2 | 1 |
| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("DATA_OBJECT_ID"=55)
rajesh@ORA12C> select * from t where object_id = 54;
Execution Plan
----------------------------------------------------------
Plan hash value: 206624438
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | 115 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
1 | 115 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX1 | 1 |
| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("OBJECT_ID"=54)
rajesh@ORA12C> select * from t where object_id = 54
2 or data_object_id = 55;
Execution Plan
----------------------------------------------------------
Plan hash value: 2971816838
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
2 | 230 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
2 | 230 | 2
(0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS |
| | | | |
| 3 | BITMAP OR | |
| | | |
| 4 | BITMAP CONVERSION FROM ROWIDS |
| | | |
|
|* 5 | INDEX RANGE SCAN | T_IDX2 | |
| 1 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS |
| | | | |
|* 7 | INDEX RANGE SCAN | T_IDX1 | |
| 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DATA_OBJECT_ID"=55)
7 -
access("OBJECT_ID"=54)
With OR Expansion, Optimizer range scan
both the index get the rowid , convert them into bitmaps and do bitwise OR
operation to find the resulting rowid’s and using those rowid’s go back to
table to find the rows.
Things
look perfect so far!
But
when IN-list come into picture, things go crazy like this. Optimizer skip using the index and go Full
scans without UNION-ALL operation.
rajesh@ORA12C> select * from t where object_id
2 in (select object_id from t1);
Execution Plan
----------------------------------------------------------
Plan hash value: 208789513
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
10 | 1180 | 14
(8)| 00:00:01 |
| 1 | NESTED LOOPS | | 10
| 1180 | 14
(8)| 00:00:01 |
| 2 | NESTED LOOPS | |
10 | 1180 | 14
(8)| 00:00:01 |
| 3 | SORT UNIQUE | |
10 | 30 | 3
(0)| 00:00:01 |
| 4 | TABLE ACCESS FULL |
T1 | 10 |
30 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_IDX1 | 1 |
| 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T |
1 | 115 | 2
(0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 -
access("OBJECT_ID"="OBJECT_ID")
Note
-----
- this is an adaptive
plan
rajesh@ORA12C> select * from t
2 wheredata_object_id =
55
3 orobject_id in (select
object_id
4 from t1);
Execution Plan
----------------------------------------------------------
Plan hash value: 1155274866
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
4504 | 505K| 544
(23)| 00:00:01 |
|* 1 | FILTER | |
| | | |
| 2 | TABLE ACCESS FULL|
T | 90056 | 9M|
544 (23)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 |
1 | 3 | 3
(0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA_OBJECT_ID"=55 OR EXISTS (SELECT 0 FROM "T1"
"T1"
WHERE "OBJECT_ID"=:B1))
3 -
filter("OBJECT_ID"=:B1)
So
here we have to explicitly do those OR expansion using UNION ALL.
rajesh@ORA12C> select * from t
2 wheredata_object_id =
55
3 union all
4 select * from t
5
wheredata_object_id<> 5
6 andobject_id in (select
object_id
7 from t1);
Execution Plan
----------------------------------------------------------
Plan hash value: 2012976192
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
11 | 1295 | 16
(7)| 00:00:01 |
| 1 | UNION-ALL | |
| | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T |
1 | 115 | 2
(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX2 | 1 |
| 1 (0)| 00:00:01 |
| 4 | NESTED LOOPS | |
10 | 1180 | 14
(8)| 00:00:01 |
| 5 | NESTED LOOPS | |
10 | 1180 | 14
(8)| 00:00:01 |
| 6 | SORT UNIQUE | |
10 | 30 | 3
(0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 |
10 | 30 | 3
(0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | T_IDX1 | 1 |
| 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | T
| 1 | 115 |
2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("DATA_OBJECT_ID"=55)
8 -
access("OBJECT_ID"="OBJECT_ID")
9 -
filter("DATA_OBJECT_ID"<>5)
Note
-----
- this is an adaptive
plan
rajesh@ORA12C> set autotrace off
So
OR Expansion is used with simple Boolean things, but not with the case of IN's
No comments:
Post a Comment