CODEWITHSUNDEEP
oracle-databaseoracle11gmaterialized-views
Roland
Roland

Reputation: 7853

What is the relation between a materialized view and the eponymous table?

I have an oracle DB with a materialized view that is populated from another oracle DB and refreshed every 2 minutes using DBMS_REFRESH.REFRESH().

I just noticed that I have a table with the same name.

  1. Does this table contain the exact same data as the materialized view?
  2. Is this table also refreshed every time the materialized view is refreshed?
  3. Is there any official Oracle documentation for this?

Thanks

Upvotes: 3

Views: 482

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

If you see both an MV and a TABLE with the same name in the user_objects data dictionary view, then It's normal behavior. That's how materialized views work. When you create a materialized view Oracle creates a table, that is going to hold MV's data, and MV specification(query and options). They both have the same name. So do not be surprised.

All that is missing would be some link to official oracle docs. :)

I do not think that the documentation states it explicitly - there is a TABLE that underpins an MV. However it does it implicitly.

Quote from the concept:

A materialized view is a query result that has been stored or "materialized" in advance as schema objects.

Moreover, materialized view can be created on an already existing table in your schema using on prebuilt table clause of the create materialized view statement - names should be the same.

Upvotes: 3

Related Questions