Oracle - FAST REFRESH Materialized views with LEFT JOINS updates very slow

Question

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).

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.

The Materialized View Log looks like this:

CREATE MATERIALIZED VIEW LOG ON Programmes_Titles
WITH PRIMARY KEY, rowid
INCLUDING NEW Values;

The Materialized View itself looks like this (it contains 700000 rows, the Programmes_Titles table contains 900000 rows):

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'

The UPDATE statement I use is this:

UPDATE Programmes_Titles 
SET Title = 'New title' 
WHERE rowid = 'AAAL4cAAEAAAftTABB'

This UPDATE statement takes 17 hours. When using an INNER JOIN (remove the (+)'s) it takes milliseconds.

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)

So my question is: Why does is take such a long time to UPDATE the underlying table? How can I improve this?

Answer 1

I've managed to reproduce your problem on a 10.2.0.3 instance. The self- and outer-join seems to be the major problem (although with indexes on every column of the MV it finally did update in under a minute).

At first I thought you could use an aggregate MV:

SQL> CREATE MATERIALIZED VIEW LOG ON Programmes_Titles
  2  WITH PRIMARY KEY, ROWID (programmeId, Titles_Group_Type, title)
  3  INCLUDING NEW Values;

Materialized view log created

SQL> CREATE MATERIALIZED VIEW Mv_Web_Programmes
  2  REFRESH FAST ON COMMIT
  3  AS
  4  SELECT ProgrammeId,
  5         MAX(decode(t1.Titles_Group_Type, 'mainTitle', t1.Title)) MainTl,
  6         MAX(decode(t1.Titles_Group_Type, 'secondaryTitle', t1.Title)) SecTl
  7    FROM Programmes_Titles t1
  8   GROUP BY ProgrammeId;

Materialized view created

Unfortunately, as you have noticed, as of 10g a MV that contains MIN or MAX can only be fast-refreshed on commit after insert (so called insert-only MV). The above solution would not work for update/delete (the MV would have to be refreshed manually).

You could trace your session and open the trace file to see what SQL query gets executed so that you can find if you can optimize it via indexes.