Improve MERGE performance when using big tables
<h3>Context</h3>

<p>We have a model in which each <code>element</code> has an <code>element kind</code> and from 0 to N <code>features</code>. Each <code>feature</code> belongs to only one <code>element</code> and has a <code>feature name</code>.</p>

<p>This is modeled as the following tables:</p>

<pre><code>ELEMENTS
  elem_id int not null -- PK
  elem_elki_id int not null -- FK to ELEMENT_KINDS
  -- more columns with elements data

ELEMENT_KINDS
  elki_id int not null -- PK
  -- more columns with elements kinds data

FEATURES
  feat_id int not null -- PK
  feat_elem_id int not null -- FK to ELEMENTS  
  feat_fena_id int not null -- FK to FEATURE_NAMES
  -- more columns with features data

FEATURE_NAMES
  fena_id int not null -- PK
  -- more columns with feature_names data
</code></pre>

<p><br/></p>

<h3>Requirement</h3>

<p>There is a new requirement of replacing the <code>feature names</code> table with a <code>feature kinds</code> table.
There is one (<strong>and only one</strong>) <code>feature kind</code> for each <code>(element kind, feature name)</code> pair.</p>

<p>The changes in the models were adding a new column and creating a new table:</p>

<pre><code>ALTER TABLE features ADD feat_feki_id int null;

CREATE TABLE FEATURE_KINDS
(
  feki_id int not null, -- PK
  feki_elki_id int not null, -- FK to ELEMENT_KINDS
  feki_fena_id int null, -- FK* to FEATURE_NAMES
  -- more columns with feature kinds data
)
</code></pre>

<p>*<code>feki_fena_id</code> is actually a temp colum showing which <code>feature name</code>
was used to create each <code>feature kind</code>. After populating <code>feat_feki_id</code>, <code>feki_fena_id</code> should be discarded along with <code>feat_fena_id</code> and the <code>feature names</code> table.</p>

<p><br/></p>

<h3>Problem</h3>

<p>After successfully populating the <code>features kinds</code> table we are trying to populate the <code>feat_feki_id</code> column using the following query:</p>

<pre><code>MERGE INTO features F
USING
(
    SELECT *
    FROM elements
    INNER JOIN feature_kinds
    ON elem_elki_id = feki_elki_id
) EFK
ON
(
    F.feat_elem_id = EFK.elem_id AND
    F.feat_fena_id = EFK.feki_fena_id
)
WHEN MATCHED THEN
UPDATE SET F.feat_feki_id = EFK.feki_id;
</code></pre>

<p>This works in small case scenarios with test data, but in production we have <strong>~20 million</strong> <code>elements</code> and <strong>~2000</strong> <code>feature_kinds</code> and it takes about an hour before throwing an <strong>ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'</strong> error.</p>

<p><br/></p>

<h3>Question</h3>

<p>Is there any way I could improve the performance of the <code>MERGE</code> so that it works? (Maybe I'm lacking some indexes?)</p>

<p>Is there another alternative to fill up the <code>feat_feki_id</code> column? (We already have tried <code>UPDATE</code> instead of <code>MERGE</code> with similar results)</p>