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<http://tie.id> from transaction_in_export tie,
transaction_version tv
where tie.transaction_version_id = tv.id<http://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
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Marián Bednár
Sent: Mittwoch, 11. April 2018 14:50
To: oracle-l@xxxxxxxxxxxxx
Subject: Why does the optimizer have problem with OR clause?
Hi List,
12.1.0.2 with Oct-2017 Bundle Patch
Why does the optimizer have problem with OR clause if two simple queries are
merged into one using OR clause?
1st query 17 consistent gets
2nd query 50 consistent gets
merged query 767216 consistent gets
Why does merged query use full table scan?
Statistics are current.
---
--- 1st query
---
select * from exchange_reason
where transaction_version_id in
(select id from transaction_version where TRANSACTION_ID = 237704 );
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 333476315
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 34 | 7 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS | |
1 | 34 | 7 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | |
1 | 34 | 7 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TRANSACTION_VERSION |
1 | 10 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | TRANSACTIONVERSIONUK |
1 | | 3 (0)| 00:00:01 | | |
|* 5 | INDEX RANGE SCAN | FI_EXC_REASON_TRAN_VER |
1 | | 2 (0)| 00:00:01 | | |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID | EXCHANGE_REASON |
1 | 24 | 3 (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TRANSACTION_ID"=237704)
5 - access("TRANSACTION_VERSION_ID"="ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
18 recursive calls
31 db block gets
17 consistent gets
0 physical reads
1296 redo size
522 bytes sent via SQL*Net to client
489 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
---
--- 2nd query
---
select * from exchange_reason
where transaction_in_export_id in
(select tie.id<http://tie.id> from transaction_in_export tie,
transaction_version tv
where tie.transaction_version_id = tv.id<http://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: 3725984717
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 37 | 18 (6)| 00:00:01 | | |
| 1 | NESTED LOOPS |
| 1 | 37 | 18 (6)| 00:00:01 | | |
| 2 | NESTED LOOPS |
| 10 | 37 | 18 (6)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1
| 1 | 13 | 6 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE |
| 1 | 20 | | | | |
| 5 | NESTED LOOPS |
| 1 | 20 | 6 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS |
| 1 | 20 | 6 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TRANSACTION_VERSION
| 1 | 10 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 8 | INDEX RANGE SCAN | TRANSACTIONVERSIONUK
| 1 | | 3 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN |
FI_TRAN_IN_EXP_TRAN_VRER | 1 | | 1 (0)| 00:00:01 | |
|
| 10 | TABLE ACCESS BY GLOBAL INDEX ROWID |
TRANSACTION_IN_EXPORT | 1 | 10 | 2 (0)| 00:00:01 | ROWID |
ROWID |
|* 11 | INDEX RANGE SCAN |
FI_EXC_REASON_TRAN_IN_EXPORT | 10 | | 1 (0)| 00:00:01 | |
|
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | EXCHANGE_REASON
| 1 | 24 | 11 (0)| 00:00:01 | ROWID | ROWID |
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TV"."TRANSACTION_ID"=237704)
9 - access("TIE"."TRANSACTION_VERSION_ID"="TV"."ID")
11 - access("TRANSACTION_IN_EXPORT_ID"="ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
117 recursive calls
29 db block gets
50 consistent gets
0 physical reads
1416 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
---
--- merged query
---
select * from exchange_reason
where transaction_version_id in
(select id from transaction_version where TRANSACTION_ID = 237704 )
OR
transaction_in_export_id in
(select tie.id<http://tie.id> from transaction_in_export tie,
transaction_version tv
where tie.transaction_version_id = tv.id<http://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: 4217242978
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
| 24 | 866K (1)| 00:00:34 | | |
|* 1 | FILTER | |
| | | | | |
| 2 | PARTITION RANGE ALL | |
299K| 7015K| 610 (2)| 00:00:01 | 1 |1048575|
| 3 | TABLE ACCESS FULL | EXCHANGE_REASON |
299K| 7015K| 610 (2)| 00:00:01 | 1 |1048575|
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID | TRANSACTION_VERSION | 1
| 10 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | PK_TRANSACTION_VERSION | 1
| | 2 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1
| 20 | 4 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_IN_EXPORT | 1
| 10 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 8 | INDEX UNIQUE SCAN | PK_TRANSACTION_IN_EXPORT | 1
| | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_VERSION | 1
| 10 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 10 | INDEX UNIQUE SCAN | PK_TRANSACTION_VERSION | 1
| | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "TRANSACTION_VERSION"
"TRANSACTION_VERSION" WHERE "ID"=:B1 AND
"TRANSACTION_ID"=237704) OR EXISTS (SELECT 0 FROM
"TRANSACTION_VERSION"
"TV","TRANSACTION_IN_EXPORT" "TIE" WHERE "TIE"."ID"=:B2 AND
"TIE"."TRANSACTION_VERSION_ID"="TV"."ID" AND
"TV"."TRANSACTION_ID"=237704))
4 - filter("TRANSACTION_ID"=237704)
5 - access("ID"=:B1)
8 - access("TIE"."ID"=:B1)
9 - filter("TV"."TRANSACTION_ID"=237704)
10 - access("TIE"."TRANSACTION_VERSION_ID"="TV"."ID")
Statistics
----------------------------------------------------------
27 recursive calls
35 db block gets
767216 consistent gets
0 physical reads
5252 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
Thanks.
Marian
____________________________________________________
Please consider the environment before printing this e-mail.
Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<html xmlns="http://www.w3.org/1999/xhtml";>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br>This message is intended only for the individual named. It may contain
confidential or privileged information. If you are not the named addressee you
should in particular not disseminate, distribute, modify or copy this e-mail.
Please notify the sender immediately by e-mail, if you have received this
message by mistake and delete it from your system.</br>
<br>E-mail transmission may not be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
processing of incoming e-mails cannot be guaranteed. All liability of the
Vontobel Group and its affiliates for any damages resulting from e-mail use is
excluded. You are advised that urgent and time sensitive messages should not be
sent by e-mail and if verification is required please request a printed
version.<br/>
</p>
</body>
</html>
