Why does dropping a MVIEW trigger a full refresh

ddlmaterialized-vieworacle

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

Best Answer

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.

Related Question