Oracle - Materialized View confusion (is Toad IDE wrong in displaying MV in Tables section?)

Question

I'm confused about Materialized views. Either it is the Toad IDE that I am using that is confusing me, or its that I don't understand MVs enough.

I created a materialized view in Oracle by something like this....

CREATE MATERIALIZED VIEW TESTRESULT
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
SELECT ...
FROM   tables...

I would expect that the materialized view would be created and populated with the data returned from the query. Okay, not a big deal.

What I am confused on is why my Toad IDE shows a table 'TESTRESULT' under the tables section. It even has a 'Create Table Script' that I can look at.

But I also see my materialized view under the 'Materialized View' section.

Behind the scenes is Oracle creating a table when I create a materialized view? It looks as if there are two seperate objects, a materialized view and a table? Could someone please explain what is going on here behind the scenes when creating a materialized view? Is Toad wrong or am I misunderstanding something?

Toad version: 9.6.1.1 Oracle: 10g

Bund

Answer 1

Yes, behind the scenes, Oracle creates two objects, a table where the results are actually materialized and a materialized view that has all the metadata (the query, the attributes, etc.). It's very similar to what happens when you create a unique constraint-- Oracle creates a unique index with the same name as the constraint to actually enforce the constraint and then it creates a constraint itself. If you create a materialized view on a pre-built table, you can end up with different names for the table and for the materialized view just as you could create a constraint that uses an existing index that has a different name.

SQL> select object_name, object_type
  2    from user_objects
  3
SQL> ed
Wrote file afiedt.buf

  1  select object_name, object_type
  2    from user_objects
  3*  where object_name = 'MV_EMP'
  4  /

no rows selected

SQL> create materialized view mv_emp
  2  as
  3  select *
  4    from emp;

Materialized view created.

SQL> column object_name format a30;
SQL> select object_name, object_type
  2    from user_objects
  3   where object_name = 'MV_EMP';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
MV_EMP                         TABLE
MV_EMP                         MATERIALIZED VIEW

Gourmont