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

• From: Stefan Koehler <contact@xxxxxxxx>
• To: oracle-l@xxxxxxxxxxxxx, marian.bednar@xxxxxxxxx
• Date: Thu, 12 Apr 2018 11:40:37 +0200 (CEST)

Hello Marián,

Why does the optimizer have problem with OR clause if  two simple queries are 
merged into one using OR clause?

Because it is simply not implemented with your current database release (still 
a heuristic based query transformation). I tried to re-model your data set 
based on your provided execution plans and SQLs and it behaves the same on 12c 
R2 (with ORE) but I have no time to check the costing and transformation right 
now :-)

By the way here is a similar case on AskTom which states the same: 
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532920100346229330

"In Oracle Database 11g Release 2 OR-Expansion is a heuristic base query 
transformation and we don’t consider statements with subqueries for 
OR-Expansion since we can not unnest the subquery that would end up in one of 
the branching. 

Starting in Oracle Database 12c R2, OR-Expansion is a cost-based 
transformation, which means both OR-Expansion and subquery unnesting could take 
place in the same query. Making it possible to be used in your example."

P.S.: If anybody has time to check ORE - here is my simple data model that I 
used on 12c R2:
--------8<---------
create table exchange_reason as select object_id as transaction_version_id, 
object_id as transaction_in_export_id from dba_objects;
create table transaction_version as select object_id as id, object_id as 
transaction_id from dba_objects;
create table transaction_in_export as select object_id as id, object_id as 
transaction_version_id from dba_objects;
create index transactionversionuk on transaction_version(transaction_id);
create index fi_exc_reason_tran_ver on exchange_reason(transaction_version_id);
create index fi_tran_in_exp_tran_vrer on 
transaction_in_export(transaction_version_id);
create index fi_exc_reason_tran_in_export on 
exchange_reason(transaction_in_export_id);
exec dbms_stats.gather_schema_stats(user);
--------8<---------

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK

Marián Bednár <marian.bednar@xxxxxxxxx> hat am 11. April 2018 um 14:50 
geschrieben: 
 

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

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

• Follow-Ups:
  • RE: Why does the optimizer have problem with OR clause?
    • From: Noveljic Nenad

• References:
  • Why does the optimizer have problem with OR clause?
    • From: Marián Bednár

Other related posts:

• » Why does the optimizer have problem with OR clause?- Marián Bednár
• » RE: Why does the optimizer have problem with OR clause?- Noveljic Nenad
• » Re: Why does the optimizer have problem with OR clause?- Marián Bednár
• » Re: Why does the optimizer have problem with OR clause?- Mladen Gogala
• » Re: Why does the optimizer have problem with OR clause? - Stefan Koehler
• » RE: Why does the optimizer have problem with OR clause?- Noveljic Nenad

1. Home
2. oracle-l
3. Archive
4. 04-2018

---

• » Previous by Date
• » Next by Date
• » Related Posts
• » View list details
• » Manage your subscription

---