带有2个左悬挂的ORACLE联接，使用LITERAL作为外部JOIN的谓词，从最左边的表中排除行 [英] ORACLE with 2 hanging left joins using LITERAL for predicate on outer JOIN exclude row from LEFT most table
问题描述
在ORACLE 12C上(这在11g上不会发生)，以下SQL在不应该的情况下从tOwners表中排除行:
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
不为"Tim"返回一行:
Does not return a row for '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")
这些谓词在这里看起来很可疑，但是为什么要这样做呢?
Those predicates look pretty suspect here, but why is it doing this?
但是，如果将"tPets.Pet ='Cat'"连接谓词更改为使用子查询，则它的工作原理就很好:
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
使用:
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"))
似乎ORACLE基于没有宠物的食物的行来限制结果集(仅当使用文字时)，但是，我认为它总是会从tOwners表返回行，无论LEFT悬挂联接中的联接谓词，有人可以解释这种行为，还是实际上是某种已知的错误?
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?
注意:我仅在ORACLE 11g和12c版本中进行过测试，到目前为止，它仅在12c中出现.
NOTE: I have only tested in ORACLE version 11g and 12c, and it only occurs in 12c so far.
推荐答案
非常有趣的观察，尽管我无法在Oracle(12.1.0.2.0版)数据库上重现它.我不得不提到我使用的是Oracle Linux 6.5，而不是Windows. 无论如何，对于这个简单但有趣的查询，也应该发布执行计划.
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 |
如您所见，优化器选择执行内部联接而不是左侧联接，这由"HASH JOIN"而不是"HASH JOIN OUTER"显示.
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.
说实话，到目前为止，我还没有听到有关此类bug的消息，所以我建议以下内容:
To be honest, I did not hear anything about a bug like this(so far), so I would suggest the following:
- 检出pfile/spfile是否包含一些未记录的参数.
- 在某些情况下，设置这些参数可以提高性能，但是在很多情况下，俗话说业力就是……"，并且您可能以一种非常糟糕的方式发生意外的执行/性能行为.
这篇关于带有2个左悬挂的ORACLE联接，使用LITERAL作为外部JOIN的谓词，从最左边的表中排除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
