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

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 12 Apr 2018 11:40:37 +0200 (CEST)
Message-ID: <1985979542.186075.1523526038069_at_ox.hosteurope.de>

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

> Marián Bednár <marian.bednar_at_gmail.com> 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

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

Received on Thu Apr 12 2018 - 11:40:37 CEST

This message: [ Message body ]
Next message: Noveljic Nenad: "RE: Why does the optimizer have problem with OR clause?"
Previous message: De DBA: "Strange deadlock problem across DB Link"
In reply to: Marián Bednár: "Why does the optimizer have problem with OR clause?"
In reply to Marián Bednár: "Why does the optimizer have problem with OR clause?"
Next in thread: Noveljic Nenad: "RE: Why does the optimizer have problem with OR clause?"

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

Original text of this message