5

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again.

The MVIEW was created with a statement like this:

create materialized view foobar
  refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;

The creation took some time.

Now after realizing it was the wrong script I ran:

drop materialized view foobar;

Which was running for some time as well. I first thought it might be due to some locks or other sessions blocking this, but no: when I queried v$session I was seeing that my drop statement actually triggered a full refresh of the mview:

USERNAME  | SID  | SERIAL# | PROGRAM                  | STATUS | SQL_ID        | SQL_TEXT
----------+------+---------+--------------------------+--------+---------------+-------------------------------------------------------------------------
MY_USER   |  294 |   29131 | oracle@hostname (J002)   | ACTIVE | 6snjgrzbtynyc | INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MY_USER"."FOOBAR" select .....

And indeed the drop materialized view ran for about the same time as the create.

So my question: why would Oracle trigger a full refresh when I drop a materialized view?

Environment: Oracle 11.2.0.3 64bit


P.S.: I think the alias indexed-view for materialized-view is wrong. Although both reference the same underlying concept they are distinct names used by Microsoft and Oracle. Someone interested in materialized views in Oracle isn't probably interested in indexed views in SQL Server. But I don't know how this alias can be removed

  • @PaulWhite: hmm, I cannot log in on meta (clicking on "Log in" simply does nothing) and when I click on "Ask Question" (without logging in) I get an error message (in the browser) that the website redirects my request so that it can never be finished. – a_horse_with_no_name Jul 31 '14 at 8:48
  • @a_horse_with_no_name you aren't running noscript or anything like that are you? Have you tried a different browser to see if it is account specific? – Jack Douglas Jul 31 '14 at 12:05
  • 4
    "oracle@hostname (J002)" probably indicates MV refresh was triggered by your refresh schedule, you didn't do it yourself. J002 means a background job process. So, likely that the mv refresh was already running when you wanted to drop. The drop wouldn't succeed until refresh was complete. – Raj Jul 31 '14 at 19:06
  • @Raj: thanks, after doing some more tests, I came up with the came conclusion. – a_horse_with_no_name Aug 1 '14 at 7:31
1

After running some tests in an isolated environment it seems that Raj is right: the refresh background job that I saw while waiting for the DROP wasn't triggered by the DROP. As far as I can tell this was caused by the refresh settings provided during the creation of the mview.

It seems that

refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour

together with the implicit build immediate causes the mview to be refreshed during the creation and then again immediately after the creation has finished.

Apparently start with trunc(sysdate) + 1/8 causes this. When running

create materialized view foobar 
   BUILD DEFERRED
   refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;
The create statement finishes quickly and when I then look into the active sessions I do see one background session that does the refresh.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.