我只是想举一个例子来解释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)
这是否正确/预期？笛卡尔的结果值是否比返回的行数更奇怪？我是否误解了这些计划,或者遗漏了一些我看不到的大事？
解决方法
根据
http://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqljusing.html
using(val)在这里将其翻译为ON tabnull.val = tabnull.val所以
using(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)
