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

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

        ID REASON                           TRANSACTION_IN_EXPORT_ID

---------- -------------------------------- ------------------------
---------------------- ---------
    234811 CAFI                                                24106
                14-MAR-18

1 row selected.

Execution Plan

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

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_at_vontobel.com>:

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

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