sql – Oracle – 连接中的表别名和NULL评估

我只是想举一个例子来解释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")

如果没有别名,它首先过滤一次表的非空值,因此只选择一行,然后它会产生第二次出现的CARTESIAN,从而得到两行;即使它是正确的,我会期望笛卡尔的结果,但我没有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)

这是否正确/预期？笛卡尔的结果值是否比返回的行数更奇怪？我是否误解了这些计划,或者遗漏了一些我看不到的大事？

解决方法

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)

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!