[20160910]低级错误.txt

--记录一个低级错误,要在表ms_yyhy中增加一个字段fyxh,并且要与同步ms_ysks.fyxh相关记录保持一直.
--我同事在测试环境下,执行如下:

UPDATE ms_yyhy
   SET fyxh =
          (SELECT fyxh
             FROM ms_ysks
            WHERE     ms_yyhy.ysdm = ms_ysks.ysdm
                  AND ms_yyhy.ksdm = ms_ysks.mzks
                  AND ms_yyhy.gzrq = TO_DATE ('2016-09-09', 'yyyy-mm-dd'))

--当时拿给我看,主要目的要评估这样在生产系统有多大影响.
--执行计划如下:
Plan hash value: 513947688
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                     |   2268K|    60M|  6813K (34)| 22:42:43 |
|   1 |  UPDATE                       | MS_YYHY             |        |       |            |          |
|   2 |   TABLE ACCESS STORAGE FULL   | MS_YYHY             |   2268K|    60M|  7772   (1)| 00:01:34 |
|*  3 |   FILTER                      |                     |        |       |            |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| MS_YSKS             |      1 |    12 |     2   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | I_MS_YSKS_MZKS_YSDM |      1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:B1=TO_DATE(' 2016-09-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("MS_YSKS"."MZKS"=:B1 AND "MS_YSKS"."YSDM"=:B2)

--我觉得不妥.我自己倒没有仔细想,感觉应该写成这样:

UPDATE ms_yyhy
   SET fyxh =
          (SELECT fyxh
             FROM ms_ysks
            WHERE ms_yyhy.ysdm = ms_ysks.ysdm AND ms_yyhy.ksdm = ms_ysks.mzks)
 WHERE ms_yyhy.gzrq = TO_DATE ('2016-09-09', 'yyyy-mm-dd');

--我还问修改的记录数量一样吗?实际上讲的是结果一样,而不是修改记录的数量.现在想想应该自己亲自测试一下,实际上对方已经发现产
--生很大的redo.第2天,我拿到itpub问是否可以优化第1种写法,幸亏ZALBB的提醒,说语句的含义不一样,我自己随手写一个脚本测试才发现
--我犯了严重错误,实际上第1种情况修改了ms_yyhy.gzrq = TO_DATE ('2016-09-09', 'yyyy-mm-dd')的同时,其他记录ms_yyhy.fyxh=NULL,
--这个错误实际太低级了.我马上讲给我同事,实际上选择执行的是我第2种写法,按照他的说法我写的执行的快,连他自己还没有明白他写
--的错误在那里.

--看来以后写dml语句先要写where条件.实际上当时我还试着其他写法,我个人喜欢的写法是:
--也是我写dml的风格,先写出查询结果:

SELECT ms_yyhy.*, ms_ysks.fyxh xx
  FROM ms_yyhy, ms_ysks
 WHERE     ms_yyhy.ysdm = ms_ysks.ysdm
       AND ms_yyhy.ksdm = ms_ysks.mzks
       AND ms_yyhy.gzrq = TO_DATE ('2016-09-07', 'yyyy-mm-dd');

--然后在对着结果集写dml语句:

update (SELECT ms_yyhy.*, ms_ysks.fyxh xx
  FROM ms_yyhy, ms_ysks
 WHERE     ms_yyhy.ysdm = ms_ysks.ysdm
       AND ms_yyhy.ksdm = ms_ysks.mzks
       AND ms_yyhy.gzrq = TO_DATE ('2016-09-07', 'yyyy-mm-dd')) a set a.fyxh=xx;

--但是当我看执行计划时在toad界面上报 ora-01779错误。

$ oerr ora 1779
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
//         map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.

--实际上这个问题很好解决,我建立的ms_ysks的索引mzks,ysdm复合索引不唯一。重新建立唯一索引后在查看执行计划就不会报错.
--这样写就可以一定程度减少差错.

--现在想想当时自己实在不清醒,看到update没写where条件应该第1反应就是修改全表,实际上对方只要看看修改记录的行数就明白问题在
--那里了.而不是仅仅关注修改的结果.实际上最后的达到的目的修改日期ms_yyhy.gzrq >= TO_DATE ('2016-09-09', 'yyyy-mm-dd');
--按照他的修改方式实际上结果也是对的,只不过产生redo大一些.因为修改不符合条件还是NULL值.

UPDATE ms_yyhy
   SET fyxh =
          (SELECT fyxh
             FROM ms_ysks
            WHERE ms_yyhy.ysdm = ms_ysks.ysdm AND ms_yyhy.ksdm = ms_ysks.mzks)
 WHERE ms_yyhy.gzrq >= TO_DATE ('2016-09-09', 'yyyy-mm-dd');

--以此为鉴,避免下次在犯类似错误.切记切记!!