RE: Why does the optimizer have problem with OR clause?
Date: Thu, 12 Apr 2018 10:09:42 +0000
Message-ID: <82592_1523527797_5ACF3075_82592_9486_1_ECDEF0CC6716EC4596FCBC871F48292AB19965F2_at_ZRH-S231>
Hello Stefan,
Because of incorrect costing, also 12.2 will discard ORE for disjunctive subqueries when it shouldn't. I'm using the test case described in http://nenadnoveljic.com/blog/disjunctive-subquery-optimization/
Best regards,
Nenad
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Koehler
Sent: Donnerstag, 12. April 2018 11:41
To: oracle-l_at_freelists.org; marian.bednar_at_gmail.com
Subject: Re: Why does the optimizer have problem with OR clause?
Hello Marián,
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
Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
Important Notice
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.
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.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^ Received on Thu Apr 12 2018 - 12:09:42 CEST