RE: Why does the optimizer have problem with OR clause?

From: Noveljic Nenad <nenad.noveljic_at_vontobel.com>
Date: Wed, 11 Apr 2018 13:05:31 +0000
Message-ID: <45257_1523451947_5ACE082B_45257_1529_1_ECDEF0CC6716EC4596FCBC871F48292AB19958FF_at_ZRH-S231>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Marián Bednár Sent: Mittwoch, 11. April 2018 14:50
To: oracle-l_at_freelists.org
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

| 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

| 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

| 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>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 11 2018 - 15:05:31 CEST

This message: [ Message body ]
Next message: Marián Bednár: "Re: Why does the optimizer have problem with OR clause?"
Previous message: Matt Adams: "RE: Tape backup"
In reply to Marián Bednár: "Why does the optimizer have problem with OR clause?"
Next in thread: Marián Bednár: "Re: Why does the optimizer have problem with OR clause?"
Reply: Marián Bednár: "Re: Why does the optimizer have problem with OR clause?"
Reply: Noveljic Nenad: "portfs and port_send_event"
Reply: Noveljic Nenad: "portfs and port_send_event"
Reply: Noveljic Nenad: "RE: Why does the optimizer have problem with OR clause?"
Reply: Noveljic Nenad: "RE: Why does the optimizer have problem with OR clause?"
Reply: Noveljic Nenad: "AW: portfs and port_send_event"
Reply: Noveljic Nenad: "AW: portfs and port_send_event"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message