[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');
--以此为鉴,避免下次在犯类似错误.切记切记!!