今天被ORACLE的NULL值耍了一上午，平时听说也了解过NULL在使用时的一些弊端，但没有仔细的考虑过一些问题，
今天算是领教了，个人感觉数据库里面还是尽量少放null值，对于需要进行比较或处理的字段，使用not null进行
下限制，要不，放个空的字段还要作为关键字段对待，意义何在？？？
两张表如下，上面没有索引：
SQL> SELECT COUNT(*) FROM product_sn_si_stage;
COUNT(*)
----------
515
SQL> SELECT COUNT(*) FROM PRODUCT_SN;
COUNT(*)
----------
450633
现在是选出product_sn_si_stage表中有而PRODUCT_SN中没有的数据，判断字段是product_sn_si_stage.sn_no和
PRODUCT_SN.SN_B_CARTAGE
我用了两种写法，结果如下
SQL> select sn_no from product_sn_si_stage
2 minus
3 select SN_B_CARTAGE from product_sn;
SN_NO
----------------
2000151973
2000151977
2000152538
2000152593
....
....
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
3 FROM product_sn_si_stage a
4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b )
6 );
COUNT(*)
----------
0
很是奇怪，这两个写法有什么不同吗？为什么结果会不一样？？？实际上第一种的结果是对的，我取了一个sn_no到PRODUCT_SN
中查找了下
SQL> select count(*) from PRODUCT_SN where SN_B_CARTAGE = '2000151973';
COUNT(*)
----------
0
确实没有出现，那为什么not in就不能出记录呢？？？？开始还怀疑是否是BUG，这么低级的BUG都会有，无意中发现了PRODUCT_SN.SN_B_CARTAGE
中存在这NULL值，而product_sn_si_stage.sn_no中经查找是没有null的，但这个不影响啊，按照惯例思维，使用not in，我这边没有null，你这边
有null，也合乎not in的意思，可错误就在这个上面
在ORACLE中NULL是不能做比较的，你不能说null = null,你也不能说null <> null，所以你也不能说 1 not in null,看看下面的例子：
SQL> select count(*) from dual where 1 not in(2,3,4,5);
COUNT(*)
----------
1
SQL> select count(*) from dual where 1 not in (2,3,4,5,null);
COUNT(*)
----------
0
1确实与集合的每个元素都不一样，但由于null的存在，返回了0行，那么我上面的第二种写法，就是因为PRODUCT_SN.SN_B_CARTAGE存在null值，导致
了查询结果的错误，NULL真的是太狡猾了，相对于这个程序而言，他的存在是致命的，而这些细微的错误往往是我们容易忽视的。
事情到这里还没有结束，奇妙的还在后面
我将PRODUCT_SN.SN_B_CARTAGE字段加上is not null判断，这样结果就会正确
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
3 FROM product_sn_si_stage a
4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b where b.sn_b_cartage is not null)
6 );
COUNT(*)
----------
367
确实，行数与上面的minus返回的一样，但这回慢了很多，大概1分钟，以前只要1秒的
如下：
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
3 FROM product_sn_si_stage a
4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b)
6 );
COUNT(*)
----------
0
Elapsed: 00:00:01.65
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=318649 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN_SI_STAGE' (TABLE) (Cost=3 Card=515 Bytes=5665)
4 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN' (TABLE) (Cost=1237 Card=22532 Bytes=247852)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
90130 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
3 FROM product_sn_si_stage a
4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b where b.sn_b_cartage is not null)
6 );
COUNT(*)
----------
367
Elapsed: 00:00:50.65
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=19462 Card=1 Bytes=10)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN_SI_STAGE' (TABLE) (Cost=3 Card=515 Bytes=5150)
4 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN' (TABLE) (Cost=1209 Card=22490 Bytes=224900)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2687673 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
仔细的比较下查询计划，路径确实是一样的，都是对两个表进行全表扫描，然后filter过滤，在进行集合运算，但时间怎么相差60倍呢？
可以看到逻辑读的差别很大，使用is not null后逻辑读增加到了2687673，调优的首要目标是减小逻辑读，可查询计划一样，路径一样，你叫
我怎么在这个上面来减小CR呢？相同的包裹寄出去，走同样的路线，价格居然不同
答案不用说，还是null在作怪，是他将查询短路了
正常思维：
PRODUCT_SN_SI_STAGE里面抽取一条
到product_sn扫描每个数据块，判断是否not in
循环直到PRODUCT_SN_SI_STAGE最后行
在这里我们理解FULL SCAN会扫描表所有的数据块，数据行，而实际上由于NULL的存在，当我们的探测行遇到null时，就表示了这个查询的失败，他注定
是不返回数据了(上面的实验已经证明)，那么他还有必要扫描下面的数据块吗？
比如
select count(*) from dual where 1 not in (2,3,4,5,null,6,7,8,9...............................);
我走过了2，3，4，5遇到了null，这样结果就一定是0了，后面的6,7,8......就没必要扫描了，短路了。
所以上面查询计划列出的TABLE ACCESS (FULL)是骗人的，每行只要遇到null就返回了，表大概只扫了一点点就退出了，这就是为什么走相同的路线，一个比
另外一个要的时间长，逻辑读要高的原因了。
在这里我们把含有null值的表的数据量扩充N倍在使用短路选择看看，按照推断，虽然你的表膨大了，但以前的NULL值还是留在那个数据块上，我做TFS时，不管
你的表有多大，遇到第一个null就返回了，所以时间应该和以前相差不了多少
SQL> INSERT /*+APPEND*/ INTO product_sn SELECT * FROM product_sn;
450633 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT /*+APPEND NOLOGGING +*/ INTO product_sn SELECT * FROM product_sn;
901266 rows created.
SQL> commit;
Commit complete.
SQL> INSERT /*+APPEND no_logging +*/ INTO product_sn SELECT * FROM product_sn;
1802532 rows created.
SQL> commit;
Commit complete.
--看看总数，顺便进行下block clear
SQL> select count(*) from product_sn;
COUNT(*)
----------
3605064
起用下我们的短路查询
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
FROM product_sn_si_stage a
3 4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b )
6 );
COUNT(*)
----------
0
Elapsed: 00:00:01.96
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=318649 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN_SI_STAGE' (TABLE) (Cost=3 Card=515 Bytes=5665)
4 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN' (TABLE) (Cost=1237 Card=22532 Bytes=247852)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
90130 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到产生的逻辑读是一样的，时间也差不多，在短路的情况下，不管后面表的大小有多大，时间都是一样的
现在回到最开始的情形，我把里面的NULL值替换成具体值
SQL> update product_sn set SN_B_CARTAGE = 'BSCY' WHERE SN_B_CARTAGE IS NULL;
1695 rows updated.
SQL> COMMIT;
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
3 FROM product_sn_si_stage a
4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b)
6 );
COUNT(*)
----------
367
Elapsed: 00:00:48.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=318649 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN_SI_STAGE' (TABLE) (Cost=3 Card=515 Bytes=5665)
4 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN' (TABLE) (Cost=1237 Card=22532 Bytes=247852)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2687673 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(*) from(
2 SELECT /*+ use_nl(a)*/trim(sn_no)
3 FROM product_sn_si_stage a
4 where sn_no not in
5 (select SN_B_CARTAGE from product_sn b where b.sn_b_cartage is not null)
6 );
COUNT(*)
----------
367
Elapsed: 00:00:51.09
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=319168 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN_SI_STAGE' (TABLE) (Cost=3 Card=515 Bytes=5665)
4 2 TABLE ACCESS (FULL) OF 'PRODUCT_SN' (TABLE) (Cost=1239 Card=22447 Bytes=246917)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2687673 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，由于NULL的消失，就不存在查询短路这个现象了，相同的逻辑读，两个查询变成一样慢了，当然这个主要是研究为什么相同计划时间不一致，
不是冲着调优去的。
从这里可以看到NULL在数据库中扮演着什么样的角色，他的存在或多或少给DBA增加了麻烦。
269
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
