CODEWITHSUNDEEP
oracle-databasejoinleft-joinoracle12c
Aaron Murgatroyd
Aaron Murgatroyd

Reputation: 1546

ORACLE with 2 hanging left joins using LITERAL for predicate on outer JOIN exclude row from LEFT most table

On ORACLE 12C (this does not occur on 11g), the following SQL excludes rows from the tOwners table when it shouldnt:

SELECT *
FROM
  (
    SELECT 1 As OwnerId, 'Fred' As OwnerName FROM DUAL UNION
    SELECT 2 As OwnerId, 'Tim' As OwnerName FROM DUAL
  ) tOwners
    LEFT JOIN
  (
    SELECT 1 As PetId, 1 As OwnerId, 'Cat' As Pet FROM DUAL UNION
    SELECT 2 As PetId, 1 As OwnerId, 'Mouse' As Pet FROM DUAL
  ) tPets
    ON
  tOwners.OwnerId = tPets.OwnerId
    LEFT JOIN
  (SELECT 2 As PetId, 'Treats' As Food FROM DUAL) tFoods
    ON
  tPets.Pet = 'Cat' AND
  tPets.PetId = tFoods.PetId

Does not return a row for 'Tim':

Invalid Results

1   Fred    1   1   Cat     
1   Fred    2   1   Mouse

Execution Plan:

Plan hash value: 3529061095

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    19 |    10   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     1 |    19 |    10   (0)| 00:00:01 |
|*  2 |   HASH JOIN        |      |     1 |    17 |     8   (0)| 00:00:01 |
|   3 |    VIEW            |      |     2 |    14 |     4   (0)| 00:00:01 |
|   4 |     SORT UNIQUE    |      |     2 |       |     4  (50)| 00:00:01 |
|   5 |      UNION-ALL     |      |       |       |            |          |
|   6 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |    VIEW            |      |     2 |    20 |     4   (0)| 00:00:01 |
|   9 |     SORT UNIQUE    |      |     2 |       |     4  (50)| 00:00:01 |
|  10 |      UNION-ALL     |      |       |       |            |          |
|  11 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|  13 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("TPETS"."PET"=CASE  WHEN (CASE  WHEN ROWID(+) IS NOT NULL 
              THEN 2 ELSE NULL END  IS NOT NULL) THEN 'Cat' ELSE 'Cat' END  AND 
              "TPETS"."PETID"=CASE  WHEN (ROWID(+) IS NOT NULL) THEN 2 ELSE NULL END )
   2 - access("TOWNERS"."OWNERID"="TPETS"."OWNERID")

enter image description here

Those predicates look pretty suspect here, but why is it doing this?

However, if you change the "tPets.Pet = 'Cat'" join predicate to use a subquery, it works just fine:

SELECT *
FROM
  (
    SELECT 1 As OwnerId, 'Fred' As OwnerName FROM DUAL UNION
    SELECT 2 As OwnerId, 'Tim' As OwnerName FROM DUAL
  ) tOwners
    LEFT JOIN
  (
    SELECT 1 As PetId, 1 As OwnerId, 'Cat' As Pet FROM DUAL UNION
    SELECT 2 As PetId, 1 As OwnerId, 'Mouse' As Pet FROM DUAL
  ) tPets
    ON
  tOwners.OwnerId = tPets.OwnerId
    LEFT JOIN
  (SELECT 2 As PetId, 'Treats' As Food FROM DUAL) tFoods
    ON
  tPets.Pet = (SELECT 'Cat' FROM DUAL) AND
  tPets.PetId = tFoods.PetId

With:

Valid Results

1   Fred    1   1   Cat     
1   Fred    2   1   Mouse       
2   Tim

Execution Plan:

Plan hash value: 1713688406

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |     2 |    56 |    16   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|                 |     2 |    56 |    16   (0)| 00:00:01 |
|*  2 |   HASH JOIN OUTER  |                 |     2 |    34 |     8   (0)| 00:00:01 |
|   3 |    VIEW            |                 |     2 |    14 |     4   (0)| 00:00:01 |
|   4 |     SORT UNIQUE    |                 |     2 |       |     4  (50)| 00:00:01 |
|   5 |      UNION-ALL     |                 |       |       |            |          |
|   6 |       FAST DUAL    |                 |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL    |                 |     1 |       |     2   (0)| 00:00:01 |
|   8 |    VIEW            |                 |     2 |    20 |     4   (0)| 00:00:01 |
|   9 |     SORT UNIQUE    |                 |     2 |       |     4  (50)| 00:00:01 |
|  10 |      UNION-ALL     |                 |       |       |            |          |
|  11 |       FAST DUAL    |                 |     1 |       |     2   (0)| 00:00:01 |
|  12 |       FAST DUAL    |                 |     1 |       |     2   (0)| 00:00:01 |
|  13 |   VIEW             | VW_LAT_9BF0EE0C |     1 |    11 |     4   (0)| 00:00:01 |
|* 14 |    FILTER          |                 |       |       |            |          |
|  15 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
|  16 |     FAST DUAL      |                 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("TOWNERS"."OWNERID"="TPETS"."OWNERID"(+))
  14 - filter("TPETS"."PETID"=2 AND "TPETS"."PET"= (SELECT 'Cat' FROM 
              "SYS"."DUAL" "DUAL"))

enter image description here

It appears as if ORACLE is limiting the result set based on rows where there are no Foods for those Pets (only when using a literal), however, I would have thought it would always return rows form the tOwners table irrespective of the join predicates on the LEFT hanging joins, can someone explain this behaviour, or is actually a known bug of some sort?

NOTE: I have only tested in ORACLE version 11g and 12c, and it only occurs in 12c so far.

Upvotes: 1

Views: 402

Answers (1)

LordOfLies
LordOfLies

Reputation: 11

Very interesting observation, although I could not reproduce it on my Oracle(version 12.1.0.2.0) database. I have to mention that I'm using Oracle Linux 6.5 and not Windows. Anyway, It would be good to post the execution plan too, for this simple, yet interesting query.

Thank you very much for posting the execution plans, this explains very well the behavior of the query. Then I shall explain, starting with the first execution plan:

|*  2 |   HASH JOIN        |      |     1 |    17 |     8   (0)| 00:00:01 |
|   3 |    VIEW            |      |     2 |    14 |     4   (0)| 00:00:01 |
|   4 |     SORT UNIQUE    |      |     2 |       |     4  (50)| 00:00:01 |
|   5 |      UNION-ALL     |      |       |       |            |          |
|   6 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |    VIEW            |      |     2 |    20 |     4   (0)| 00:00:01 |
|   9 |     SORT UNIQUE    |      |     2 |       |     4  (50)| 00:00:01 |
|  10 |      UNION-ALL     |      |       |       |            |          |
|  11 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |       FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |

As you can see, the optimizer chooses to do an inner join, instead of the left join, and that is showed by the "HASH JOIN" and not "HASH JOIN OUTER" as it should be.

To be honest, I did not hear anything about a bug like this(so far), so I would suggest the following:

  • Check out the pfile/spfile if it contains some undocumented parameters.
  • There are cases when setting these parameters can improve the performance, but a lot of times, "karma is ...", as the saying goes, and you can have unexpected execution/performance behaviors in a really really bad way.

Upvotes: 1

Related Questions