index for better performance for a merge operation

A merge adds to certain contract data further information from another table, to have all the necessary criteria in one table for further operations. For this purpose, a merge is used. This takes a comparatively long. KundenNr is an index column in both tables. So here the question: (How) could this Merge be accelerated with another index columns?

(Ein Merge fügt zu bestimmten Vertragsdaten weitere aus einer anderen Tabelle hinzu, um alle notwendigen Kriterien in einer Tabelle für weitere Operationen vorzuhalten. Dazu wird ein Merge genutzt. Dieser dauert vergleichsweise lange. KundenNr ist in beiden Tabellen eine Indexspalte. Daher die Frage, (wie) könnte man diesen Merge mit weiteren Indexspalten beschleunigen?) 

Merge into EinzelVertraege1412 EV
USING (SELECT * FROM Personendaten1412) PD
ON (EV.KundenNr= PD.KundenNr  AND Herkunft= 'Topf1')
    when matched then UPDATE
    SET EV.Betreuer=PD.Betreuer,
        EV.Experte=PD.Experte,
        EV.Service=PD.Service,
        EV.Kundengruppe=PD.Kundengruppe,
        EV.Klasse=PD.Klasse,
        EV.Verfahren=PD.Verfahren,
        EV.Cluster=PD.Cluster
I was recommended to index all columns involved in the merge, but that does not seem to make sense to me, because in the table EinzelVertraege, the index would have to be updated permanently...

(Mir wurde empfohlen, alle am Merge beteiligten Spalten zu indizieren, aber das scheint mir nicht sinnvoll, da in der Tabelle Einzelverträge dann ja der Index ständig aktualisiert werden müsste...)

Thanks in advance :)

asked Jan 29 '15 at 11:55

antilet's gravatar image

antilet
1●1●1●2

Just from seeing the query, it is hard to tell where the time is lost. Can you please add an execution plan? I'd guess this is from an Oracle DB, in that case it would be best to run the query and provide the output of select * from table(dbms_xplan.display_cursor()) afterwards.

(Jan 30 '15 at 20:10) Markus Winand ♦♦

Yes, this is from an Oracle DB. I'll try to get the information, but this is a little difficult, because i can only work on the database via SQL-Passthrough. Does it matter if the merge was already done and I rerun it again or will then the execution be different?

(Feb 03 '15 at 09:13) antilet
2 Answers:

So, here we are. It took a little while, due to some restrictions in access to the database i had to struggle with a little PL/SQL. For the test i used a very small merge... 

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |                          |       |       |   215K(100)|          |
|   1 |  MERGE                         | EinzelVertraege1412      |       |       |            |          |
|   2 |   VIEW                         |                          |       |       |            |          |
|   3 |    NESTED LOOPS                |                          |       |       |            |          |
|   4 |     NESTED LOOPS               |                          | 19907 |    11M|   215K  (1)| 00:21:32 |
|*  5 |      TABLE ACCESS FULL         | EinzelVertraege1412      | 19883 |  7359K|   155K  (1)| 00:15:33 |
|*  6 |      INDEX RANGE SCAN          | IXKundenNR_PersDat1412   |     1 |       |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| Personendaten1412        |     1 |   216 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


"   5 - filter(""Herkunft""='Topf1')"
"   6 - access(""EV"".""KundenNr""=""Personendaten1412"".""KundenNr"")"

answered Feb 13 '15 at 10:43

antilet's gravatar image

antilet
1●1●1●2

edited Feb 13 '15 at 10:45

The eye-catching point in above-posted execution-plan is the Table Access Full due to the filter. So I tried an index on the column "Herkunft". For me - as an novice - the result looks much better. But one question left: is there still a possibility to improve the performance?

Thanks in advance for your time reading and thinking about my questions.

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                 |                          |       |       | 60262 (100)|          |
|   1 |  MERGE                          | EinzelVertraege1412      |       |       |            |          |
|   2 |   VIEW                          |                          |       |       |            |          |
|   3 |    NESTED LOOPS                 |                          |       |       |            |          |
|   4 |     NESTED LOOPS                |                          | 19907 |    11M| 60262   (1)| 00:06:02 |
|   5 |      TABLE ACCESS BY INDEX ROWID| EinzelVertraege1412      | 19883 |  7359K|   570   (2)| 00:00:04 |
|*  6 |       INDEX RANGE SCAN          | IXHerkunft_1412          | 19883 |       |    60   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | IXKundenNR_PersDat1412   |     1 |       |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID | Personendaten1412        |     1 |   216 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
"   6 - access(""Herkunft""='Topf1')"
"   7 - access(""EV"".""KundenNr""=""Personendaten1412"".""KundenNr"")"

answered Feb 20 '15 at 11:40

antilet's gravatar image

antilet
1●1●1●2

edited Feb 20 '15 at 11:43