Nenad, thanks. Query with UNION ALL works fine...
select * from exchange_reason
where transaction_version_id in
(select id from transaction_version where TRANSACTION_ID = 237704 )
UNION ALL
select * from exchange_reason
where
transaction_in_export_id in
(select tie.id from transaction_in_export tie,
transaction_version tv
where tie.transaction_version_id = tv.id and tv.TRANSACTION_ID
= 237704 );
ID REASON TRANSACTION_IN_EXPORT_ID
TRANSACTION_VERSION_ID ACTUAL_TR
---------- -------------------------------- ------------------------
---------------------- ---------
234811 CAFI 24106
14-MAR-18
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 387586521
------------------------------------------------------------
------------------------------------------------------------
--------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------
------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT |
| 2 | 71 | 25 (4)| 00:00:01 | | |
| 1 | UNION-ALL |
| | | | | | |
| 2 | NESTED LOOPS |
| 1 | 34 | 7 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS |
| 1 | 34 | 7 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED |
TRANSACTION_VERSION | 1 | 10 | 4 (0)| 00:00:01 |
ROWID | ROWID |
|* 5 | INDEX RANGE SCAN |
TRANSACTIONVERSIONUK | 1 | | 3 (0)| 00:00:01 |
| |
|* 6 | INDEX RANGE SCAN |
FI_EXC_REASON_TRAN_VER | 1 | | 2 (0)| 00:00:01 |
| |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID |
EXCHANGE_REASON | 1 | 24 | 3 (0)| 00:00:01 |
ROWID | ROWID |
| 8 | NESTED LOOPS |
| 1 | 37 | 18 (6)| 00:00:01 | | |
| 9 | NESTED LOOPS |
| 10 | 37 | 18 (6)| 00:00:01 | | |
| 10 | VIEW | VW_NSO_1
| 1 | 13 | 6 (0)| 00:00:01 | | |
| 11 | HASH UNIQUE |
| 1 | 20 | | | | |
| 12 | NESTED LOOPS |
| 1 | 20 | 6 (0)| 00:00:01 | | |
| 13 | NESTED LOOPS |
| 1 | 20 | 6 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED|
TRANSACTION_VERSION | 1 | 10 | 4 (0)| 00:00:01 |
ROWID | ROWID |
|* 15 | INDEX RANGE SCAN |
TRANSACTIONVERSIONUK | 1 | | 3 (0)| 00:00:01 |
| |
|* 16 | INDEX RANGE SCAN |
FI_TRAN_IN_EXP_TRAN_VRER | 1 | | 1 (0)| 00:00:01 |
| |
| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID |
TRANSACTION_IN_EXPORT | 1 | 10 | 2 (0)| 00:00:01 |
ROWID | ROWID |
|* 18 | INDEX RANGE SCAN |
FI_EXC_REASON_TRAN_IN_EXPORT | 10 | | 1 (0)| 00:00:01 |
| |
| 19 | TABLE ACCESS BY GLOBAL INDEX ROWID |
EXCHANGE_REASON | 1 | 24 | 11 (0)| 00:00:01 |
ROWID | ROWID |
------------------------------------------------------------
------------------------------------------------------------
--------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TRANSACTION_ID"=237704)
6 - access("TRANSACTION_VERSION_ID"="ID")
15 - access("TV"."TRANSACTION_ID"=237704)
16 - access("TIE"."TRANSACTION_VERSION_ID"="TV"."ID")
18 - access("TRANSACTION_IN_EXPORT_ID"="ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
132 recursive calls
33 db block gets
66 consistent gets
0 physical reads
1660 redo size
642 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2018-04-11 15:05 GMT+02:00 Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>:
It would be interesting to see whether the index get used if you rewrite
the OR query as UNION ALL query, like this:
select * from exchange_reason
where transaction_version_id in
(select id from transaction_version where TRANSACTION_ID =
237704 )
UNION ALL
select * from exchange_reason
where
transaction_in_export_id in
(select tie.id from transaction_in_export tie,
transaction_version tv
where tie.transaction_version_id = tv.id and
tv.TRANSACTION_ID = 237704 );
As far as I know, the rule based OR transformation (12.1 and below)
doesn’t transform disjunctive subqueries into set operations.
Interestingly, SQL Server optimizer is able to do this kind of
optimization, see http://nenadnoveljic.com/blog/
disjunctive-subquery-optimization/
As of 12.2, Oracle implemented OR transformation as cost based. However,
as the plans are not being costed properly for disjunctive subqueries, the
transformation will still not be performed.
Best regards,
Nenad
