[20150803]触发器对dml的影响.txt
--最近做一个优化项目,这个项目实际上ETL项目,里面出现如下语句:
UPDATE patient_medical_cost t
SET t.total_costs = NVL (total_costs, 0),
t.drug_west_costs = NVL (drug_west_costs, 0),
t.drug_middle_costs = NVL (drug_middle_costs, 0),
t.drug_anti_costs = NVL (drug_anti_costs, 0)
WHERE total_costs IS NULL
OR drug_west_costs IS NULL
OR drug_middle_costs IS NULL
OR drug_anti_costs IS NULL;
--这个本来应该在整合的抽取时就应该做好设置为0,根本不应该在进入表在做处理,而是通过索引的方式来处理不是很好.
--目前我想测试看看触发器对插入的影响.
1.建立测试环境:
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t (id number ,x1 number ,x2 number ,x3 number ,x4 number);
2.无触发器的情况:
set timing on
insert into t select rownum ,0,0,0,0 from xmltable('1 to 10000000');
truncate table t
commit ;
--3次测试(第1次忽略,实际上也是这个时间):
Elapsed: 00:00:34.28
Elapsed: 00:00:36.28
Elapsed: 00:00:35.68
Elapsed: 00:00:32.21
4.建立触发器:
--从我自己管理数据库的角度,我非常不喜欢触发器.我一直认为这种情况是不得以而为之.
CREATE OR REPLACE TRIGGER tri_t_null_0
BEFORE INSERT
ON T
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
:NEW.X1 := nvl(:new.x1,0);
:NEW.X2 := nvl(:new.x2,0);
:NEW.X3 := nvl(:new.x3,0);
:NEW.X4 := nvl(:new.x4,0);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20002, 'insert into t error!');
RAISE ;
END tri_t_null_0;
/
set timing on
insert into t select rownum ,NULL,NULL,NULL,NULL from xmltable('1 to 10000000');
truncate table t
commit ;
Elapsed: 00:01:18.35
Elapsed: 00:01:20.09
Elapsed: 00:01:19.66
5.对比可以发现:
79/34=2.3,使用触发器会慢2.3倍,看来不能考虑触发器的解决方式.
6.12c有1个新特性,可以让缺省值等于特定值.也测试看看:
SCOTT@test01p> drop trigger tri_t_null_0;
Trigger dropped.
alter table t modify(x1 default on null 0);
alter table t modify(x2 default on null 0);
alter table t modify(x3 default on null 0);
alter table t modify(x4 default on null 0);
set timing on
insert into t select rownum ,NULL,NULL,NULL,NULL from xmltable('1 to 10000000');
truncate table t
commit ;
Elapsed: 00:00:32.13
Elapsed: 00:00:28.46
Elapsed: 00:00:33.14
--基本与无触发器一致.
SCOTT@test01p> @ddl scott.t
C100
--------------------------------------------------------
CREATE TABLE "SCOTT"."T"
( "ID" NUMBER,
"X1" NUMBER DEFAULT 0 NOT NULL ENABLE,
"X2" NUMBER DEFAULT 0 NOT NULL ENABLE,
"X3" NUMBER DEFAULT 0 NOT NULL ENABLE,
"X4" NUMBER DEFAULT 0 NOT NULL ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;