merge into怎么比查询判断update还是insert效率还低?
下面是其中一条sql语句,存在就update否则insert数据,deviceid 、upt_date 建立了索引,数据是一条一条update或者insert
merge into (select * from t_his_temperature where deviceid = 66594 and upt_date = '2017-12-03 23:56:06') t1 using (select 66594 as deviceid,'2017-12-03 23:56:06' as upt_date FROM dual) t2 on (t1.deviceid = t2.deviceid and t1.upt_date=t2.upt_date) when matched then update set humidity=91 where t1.deviceid = 66594 and t1.upt_date = '2017-12-03 23:56:06' when not matched then insert (id,deviceid,humidity,upt_date) values (SEQ_his_temp.NEXTVAL,66594,91,'2017-12-03 23:56:06')
执行计划:
Plan Hash Value : 2657218553
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 122 | 3 | 00:00:01 |
| 1 | MERGE | T_HIS_TEMPERATURE | | | | |
| 2 | VIEW | | | | | |
| 3 | SEQUENCE | SEQ_HIS_TEMPERATURE | | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 101 | 3 | 00:00:01 |
| 5 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 | 00:00:01 |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID | T_HIS_TEMPERATURE | 1 | 99 | 1 | 00:00:01 |
| * 7 | INDEX UNIQUE SCAN | PK_HIS_TEMP_DATE | 1 | | 2 | 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 7 - access("DEVICEID"(+)=66594 AND "UPT_DATE"(+)=U'2017-12-03 23:56:06')
* 7 - filter("T_HIS_TEMPERATURE"."UPT_DATE"(+)=SYS_OP_C2C(CASE WHEN ROWID IS NOT NULL THEN '2017-12-03 23:56:06' ELSE '2017-12-03 23:56:06' END ) AND "T_HIS_TEMPERATURE"."DEVICEID"(+)=CASE WHEN
(ROWID IS NOT NULL) THEN 66594 ELSE 66594 END )
Note
-----
- dynamic sampling used for this statement