title: Oracle - FAST REFRESH Materialized views with LEFT JOINS updates very slow
body: <p>I have a Materialized view in Oracle that contains a LEFT JOIN which takes a very long time to update. When I update the underlying table it takes 63914.765 s to run (yes that is almost 17 hours).</p>
  
  <p>I am using a LEFT JOIN on the same table, because I want to pivot the data from rows to columns. The pivot command is not available in this Oracle version, and using a GROUP BY + CASE is not allowed on a FAST REFRESH Materialized View.</p>
  
  <p>The Materialized View Log looks like this:</p>
  
  <pre><code>CREATE MATERIALIZED VIEW LOG ON Programmes_Titles
  WITH PRIMARY KEY, rowid
  INCLUDING NEW Values;
  </code></pre>
  
  <p>The Materialized View itself looks like this (it contains 700000 rows, the Programmes_Titles table contains 900000 rows):</p>
  
  <pre><code>CREATE MATERIALIZED VIEW Mv_Web_Programmes
  REFRESH FAST ON COMMIT 
  AS
  
  SELECT
      t1.ProgrammeId,        
      t1.Title as MainTitle,
      t2.Title as SecondaryTitle,
      --Primary key
      t1.Title_Id as t1_titleId,
      t2.Title_Id as t2_titleId,
  
      t1.rowid as t1_rowid,
      t2.rowid as t2_rowid
  FROM
      Programmes_Titles t1, 
      Programmes_Titles t2
  WHERE
      t1.Titles_Group_Type = 'mainTitle'
      AND t1.Programme_Id = t2.Programme_Id(+) AND t2.Titles_Group_Type(+) = 'secondaryTitle'
  </code></pre>
  
  <p>The UPDATE statement I use is this:</p>
  
  <pre><code>UPDATE Programmes_Titles 
  SET Title = 'New title' 
  WHERE rowid = 'AAAL4cAAEAAAftTABB'
  </code></pre>
  
  <p>This UPDATE statement takes 17 hours.
  When using an INNER JOIN (remove the (+)'s) it takes milliseconds.</p>
  
  <p>I also tried adding INDEXES on the Mv_Web_Programmes Materialized View, but that did not seem to help either. (It still runs for more than a minute, which is way to slow, I am not waiting 17 hours after every change, so it might improved the UPDATE)</p>
  
  <p>So my question is: Why does is take such a long time to UPDATE the underlying table? How can I improve this?</p>