SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bug with outer join, or & analytic function (or rownum)

Jan LeersSep 29 2015 — edited Sep 30 2015

Hi,

Seems like the combination of outer join, OR and rownum confuses the CBO.

First query is without rownum, second is with rownum.

The second query expects 203T of rows, and never finishes. It should behave the same as query 1, with 24M of rows .

Removing the OR-clause from the query 2 lets it behave as query 1, with 24M rows.

Anyone ever seen this? Is there a workaround?

SELECT *
FROM message i
LEFT JOIN (SELECT hi.message_id, hi.update_dt
               FROM message_hist hi) h ON (t.id = h.master_id
                                       AND(t.update_dt = h.update_dt OR h.update_dt <TO_DATE('150901','RRMMDD')));

-----------------------------------------------------------------------------------------------
| Id | Operation           | Name                    | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                         |    24M|    13G|   475G (2)|999:59:59 |
|   1 | NESTED LOOPS OUTER |                         |    24M|    13G|   475G (2)|999:59:59 |
|   2 |   TABLE ACCESS FULL | MESSAGE                 | 8037K| 1318M| 29883   (2)| 00:06:59 |
|   3 |   VIEW              |                         |     3 | 1302 | 59136   (2)| 00:13:48 |
|* 4 |    TABLE ACCESS FULL| MESSAGE_HIST            |     3 |   168 | 59136   (2)| 00:13:48 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("I"."MESSAGE_ID"="HI"."MESSAGE_ID" AND
              ("HI"."UPDATE_DT"<TO_DATE('150901','RRMMDD') OR "I"."UPDATE_DT"="HI"."UPDATE_DT"))

----------------

SELECT *
FROM message i
LEFT JOIN (SELECT hi.message_id, hi.update_dt
                  , ROWNUM
               FROM message_hist hi) h ON (t.id = h.master_id
                                       AND(t.update_dt = h.update_dt OR h.update_dt <TO_DATE('150901','RRMMDD')));

-------------------------------------------------------------------------------------------------
| Id | Operation             | Name                    | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |   203T|   112P|   475G (2)|999:59:59 |
|   1 | NESTED LOOPS OUTER   |                         |   203T|   112P|   475G (2)|999:59:59 |
|   2 |   TABLE ACCESS FULL   | MESSAGE                 | 8037K| 1318M| 29883   (2)| 00:06:59 |
|   3 |   VIEW                |                         |    25M|    10G| 59151   (2)| 00:13:49 |
|* 4 |    VIEW               |                         |    25M|    10G| 59151   (2)| 00:13:49 |
|   5 |     COUNT             |                         |       |       |            |          |
|   6 |      TABLE ACCESS FULL| MESSAGE_HIST            |    25M| 1355M| 59151   (2)| 00:13:49 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("I"."MESSAGE_ID"="H"."MESSAGE_ID" AND ("I"."UPDATE_DT"="H"."UPDATE_DT" OR
              "H"."UPDATE_DT"<TO_DATE('150901','RRMMDD')))

This post has been answered by AndrewSayer on Sep 29 2015

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 28 2015

Added on Sep 29 2015

12 comments

1,391 views