5

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?

Tejo
  • 547
  • 4
  • 15

2 Answers2

3

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.

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • Thank you, the suggested solution works perfect. Just one additional question. In my example query I only use a single criteria in the JOIN, but my real query uses two. Can I still use the DECODE function (this is one new for me or should I use a CASE instead?) – Tejo Nov 21 '11 at 12:05
  • Sorry, I was being a bit quick. Programme_Titles table gets updated with the new value under a second, but the Mv_Web_Programmes is not updated, the old value is still there. Any ideas? – Tejo Nov 21 '11 at 12:11
  • You should be able to use CASE instead of DECODE as long as every column you use in your CASE statement is also in the MV log. – Vincent Malgrat Nov 21 '11 at 13:42
  • You're right the MV isn't updated after update/delete, see http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref534 – Vincent Malgrat Nov 21 '11 at 14:04
  • Ok, thank you for your information. It seems it is not possible to get an updated view when using MAX. The self/outer join solution is not fast enough either. I can't use PIVOT since 10g doesn't support it. I will try to think of another solution, maybe without using materialized views. You'll get my vote and answer since you did answer my question. – Tejo Nov 21 '11 at 14:22
2

We too faced this issue recently on Oracle 11.2.0.3

In our case, it was unavoidable to to remove an 'OUTER JOIN' due to functional impact.

On investigation, it was found that Oracle was adding a nasty HASH_SH (Hash Semi Join) hint with MV refresh DML.

Nothing worked including things mentioned in following blog- http://www.adellera.it/blog/2010/03/11/fast-refresh-of-join-only-mvs-_mv_refresh_use_stats-and-locking-log-stats/#comment-2975

In the end, a hidden hint worked...(though in general, it should be avoided by making change in application if possible)

Oracle Doc ID 1949537.1 suggests that setting the hidden _mv_refresh_use_hash_sj parameter to FALSE should prevent it using that hint.

alter session set "_mv_refresh_use_hash_sj"=FALSE;

That stopped CBO using the HASH_SJ hint.

Posting it here in the interests of others.

pahariayogi
  • 1,073
  • 1
  • 7
  • 18