MERGE INTO Performance

Question

I have a table contains tat contains {service_id, service_name,region_name}

As input my procedure gets service_id , i_svc_region list of key,value pairs, which has {service_name, region}.

Have to insert into the table if the record does not exists already. I know it is a very simple query.. But does the below queries make any difference in performance?

which one is better and why?

MERGE INTO SERVICE_REGION_MAP table1
USING 
    (SELECT i_svc_region(i).key as service_name,i_enabled_regions(i).value as region
         FROM dual) table2
ON (table1.service_id =i_service_id and table1.region=table2.region)    
WHEN NOT MATCHED THEN       
    INSERT (service_id,service_name ,region) VALUES (i_service_id ,table2.service_name,table2.region);

i_service_id - is passed as it is.

MERGE INTO SERVICE_REGION_MAP table1
USING 
    (SELECT i_service_id as service_id, i_svc_region(i).key as service_name,i_enabled_regions(i).value as region
         FROM dual) table2
ON (table1.service_id =table2.service_id and table1.region=table2.region)    
WHEN NOT MATCHED THEN       
    INSERT (service_id,service_name ,region) VALUES (table2.service_id,table2.service_name,table2.region);

i_service_id is considered as column in table.

Does this really make any difference?

It's unlikely to make any appreciable difference. However, if I were you and the rest of the code made it possible, I would do my best to try and get all the merges done in one go - i.e. rather than looping over the array, I would select from the array (after making the array type global via `create or replace type ...`), if it's not already global) instead and do all the merging in one go. — Boneist, Jul 20 '18 at 12:40

score 0 · Answer 1 · answered Jul 20 '18 at 17:07

0

You should be using the FORALL statement. It will result in much faster performance than any looping we could write. Check out the documenation, starting with https://docs.oracle.com/database/121/LNPLS/forall_statement.htm#LNPLS01321

answered Jul 20 '18 at 17:07

Brian Leach

2,025
1
11
14

score 0 · Answer 2 · answered Jul 20 '18 at 18:18

As @Brian Leach suggests the FORALL will give you a single round trip to SQL engine for all of the elements (i's) in your table. This can give between 10 and 100 times improvement depending on table size and many other things beyond me.

Also you are only using the INSERT capability of MERGE so a time honoured INSERT statement should make life easier/faster for the database. MERGE has more bells and whistles which can slow it down.

So try something like: FORALL i IN 1..i_svc_region(i).COUNT INSERT INTO SERVICE_REGION_MAP table1 (service_id, service_name, region) SELECT i_service_id AS service_id, i_svc_region(i).KEY AS service_name, i_enabled_regions(i).VALUE AS region FROM DUAL table2 WHERE NOT EXISTS ( SELECT * FROM SERVICE_REGION_MAP table1 WHERE table1.service_id=table2.service_id AND table1.region=table2.region );

MERGE INTO Performance

2 Answers2