For a business logic, you must first determine whether a record exists in the Database. If yes, update the record. If no record exists, insert the record.
The practice before the application is:
1. Use conditions to determine the number of records in the database.
2.1 If count (*)> 0, update is performed.
2.2 If count (*) = 0, the insert operation is executed.
Or
1. insert records first.
2.1 If a primary key error occurs for the ORA-001, a record exists and the update operation is performed.
2.2 If no error is reported, the insertion is considered complete.
I think the above two methods can implement this business logic. The difference is that the second method may only require one SQL operation, provided that most records do not exist. If most operations are update operations, this can be changed:
1. Update first.
2.1 if the number of updates is greater than 0, a record exists and execution is complete.
2.2 If the number of updates is 0, no record exists and the insert operation is executed.
The worst case of the above logic is to execute two SQL statements. If the data volume is small, the time consumed can be ignored. However, if the data volume is large, the time consumed may be doubled. In this case, you may consider using merge. Generally, merge is used to import data from one table to another, but it can operate on the same table. For example:
Requirement: rule_collision table: update the collision_count Field Based on app_name, rule_id, and start_time, or insert a new record directly.
SQL:
Merge into rule_collision T1
Using (select 'test' app_name, 'timelimit _ component 'module, 'Rule 1 'rule__id, 3 collision_count, to_date ('2017-07-21', 'yyyy-mm-dd ') start_time from dual) T2
On (t1.app _ name = t2.app _ name and t1.rule _ id = t2.rule _ id and t1.start _ time = t2.start _ time)
When matched then
Update set t1.collision _ COUNT = t2.collision _ count
When not matched then
Insert values (t2.app _ name, t2.module, t2.rule _ id, t2.collision _ count, t2.start _ time );
Using the pseudo table dual to implement self-update or insert of the rule_collision table is the same as the above logic, but only one SQL statement is executed as follows:
Execution Plan
----------------------------------------------------------
Plan hash value: 3989089639
Bytes -----------------------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes -----------------------------------------------------------------------------------------------------------
| 0 | merge statement | 1 | 322 | 2 (0) | 00:00:01 |
| 1 | merge | ri_rule_collision_detail |
| 2 | View |
| 3 | nested loops outer | 1 | 224 | 2 (0) | 00:00:01 |
| 4 | fast dual | 1 | 2 (0) | 00:00:01 |
| 5 | table access by index rowid | ri_rule_collision_detail | 1 | 224 | 0 (0) | 00:00:01 |
| * 6 | index unique scan | rule_collision_id | 1 | 0 (0) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------------------------------
Predicate information (identified by Operation ID ):
---------------------------------------------------
6-access ("T1 ". "app_name" (+) = 'galt' and "T1 ". "rule_id" (+) = 'Rule 1' and "T1 ". "start_time" (+) = to_date ('2014-07-21 00:00:00 ', 'syyyy-mm-dd hh24: MI: ss '))
Note
-----
-Dynamic sampling used for this statement (Level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
3 dB block gets
2 consistent gets
0 physical reads
0 redo size
829 bytes sent via SQL * Net to client
1315 bytes encoded ed via SQL * Net From Client
3 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
1 rows processed
Here we use nested loops outer instead of hash join. Is the two table operation different? You need to study this experiment later. If you have any experts, please consult!
