ORACLE有2个挂起的左连接，使用文字作为外连接上的谓词，排除最左侧表中的行

Question

在Oracle12C上(11g上不会发生这种情况)，以下SQL会在不应该排除tOwners表中的行时将其排除：

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

不返回‘Tim’的行：

​

​

1   Fred    1   1   Cat     
1   Fred    2   1   Mouse   

执行计划：

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")

​

​

这些谓词在这里看起来很可疑，但它为什么要这样做呢？

但是，如果您将"tPets.Pet = 'Cat'“连接谓词更改为使用子查询，则可以很好地工作：

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

通过以下方式：

​

​

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

执行计划：

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"))

​

​

看起来甲骨文似乎是在限制结果集的行，其中没有这些宠物的食物(仅当使用文字时)，然而，我会认为它总是从tOwners表中返回行，而不管左侧挂起的连接上的连接谓词，有人能解释这种行为吗，或者实际上是某种已知的bug？

注意:我只在ORACLE版本11g和12c中进行了测试，到目前为止，它只在12c中出现。

Answer 1

非常有趣的观察，尽管我不能在我的Oracle(version 12.1.0.2.0)数据库上重现它。我不得不提一下，我使用的是Oracle Linux 6.5，而不是Windows。无论如何，对于这个简单而有趣的查询，发布执行计划也是很好的。

非常感谢您发布执行计划，这很好地解释了查询的行为。然后我将从第一个执行计划开始解释：

|*  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 |

正如您所看到的，优化器选择进行内连接，而不是左连接，这由"HASH join“而不是"HASH join not”显示。

老实说，(到目前为止)我还没有听说过这样的bug，所以我会提出以下建议：

如果pfile/spfile包含一些未记录的parameters.

• There，请查看pfile/spfile。设置这些参数可以提高性能，但很多时候，正如俗话所说，“ is ..."，您可能会以非常糟糕的方式出现意想不到的执行/性能行为。