sql - Oracle - 连接中的表别名和 NULL 计算

Question

我只是想举一个例子来解释 Oracle 中的 NULL 如何导致“意外”行为，但我发现了一些我没有想到的东西......

设置:

create table tabNull (val varchar2(10), descr varchar2(100));
insert into tabNull values (null, 'NULL VALUE');
insert into tabNull values ('A', 'ONE CHAR');

这给出了我的预期:

SQL> select * from tabNull T1 inner join tabNull T2 using(val);

VAL        DESCR                DESCR
---------- -------------------- --------------------
A          ONE CHAR             ONE CHAR

如果我删除表别名，我会得到:

SQL> select * from tabNull inner join tabNull using(val);

VAL        DESCR                DESCR
---------- -------------------- --------------------
A          ONE CHAR             ONE CHAR
A          ONE CHAR             ONE CHAR

这让我很惊讶。

从这两个查询的执行计划中可以找到原因；使用表别名，Oracle 会进行 HASH JOIN，然后检查 T1.val = T2.val:

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     1 |   118 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |         |     1 |   118 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TABNULL |     2 |   118 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TABNULL |     2 |   118 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("T1"."VAL"="T2"."VAL")

如果没有别名，它首先会过滤表中出现的一个非空值，从而仅选择一行，然后对第二次出现的情况进行笛卡尔计算，从而给出两行；即使它是正确的，我也会期望笛卡尔的结果，但我没有任何 DESCR = 'NULL VALUE' 的行。

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     2 |   118 |     6   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|         |     2 |   118 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | TABNULL |     1 |    59 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |         |     2 |       |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | TABNULL |     2 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("TABNULL"."VAL" IS NOT NULL)

这在某种程度上是正确的/预期的吗？笛卡尔的结果值是不是比返回的行数更奇怪？我是否误解了计划，或者错过了一些我看不到的大事？

Answer 1

根据http://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqljusing.htmlusing(val) 此处翻译为 ON tabnull.val=tabnull.val 所以

select tabNull.*, tabNull.descr from tabNull inner join tabNull 
on tabNull.val = tabNull.val;

接下来要制定计划，Oracle 必须[实际上]为每个 JOIN 成员分配不同的别名，但认为没有理由在 SELECT 和 ON 中的任何位置使用第二个别名。所以

select t1.*, t1.descr from tabNull t1 inner join tabNull t2 
on t1.val = t1.val;

计划

--------------------------------------------------------------------------------                        
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                        
--------------------------------------------------------------------------------                        
|   0 | SELECT STATEMENT     |         |     2 |    28 |     4   (0)| 00:00:01 |                        
|   1 |  MERGE JOIN CARTESIAN|         |     2 |    28 |     4   (0)| 00:00:01 |                        
|*  2 |   TABLE ACCESS FULL  | TABNULL |     1 |    14 |     2   (0)| 00:00:01 |                        
|   3 |   BUFFER SORT        |         |     2 |       |     2   (0)| 00:00:01 |                        
|   4 |    TABLE ACCESS FULL | TABNULL |     2 |       |     2   (0)| 00:00:01 |                        
--------------------------------------------------------------------------------                        


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

   2 - filter("T1"."VAL" IS NOT NULL)