Materialized view and table with the same name Materialized view and table with the same name oracle oracle

Materialized view and table with the same name


From the documentation:

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data.

So having the table and materialized view with the same name is normal. The MV needs to store the data somewhere, so having a table makes sense; the MV itself then defines how the table data is maintained.

You can use the ON PREBUILT TABLE clause to create a view over an existing table, which I assume is what "they had a temp table earlier ... and switched to Materialized view later" refers to.

You can also go the other way, with the DROP MATERIALIZED VIEW ... PRESERVE TABLE option, which leaves the underlying table behind.

When you SELECT * FROM TEMP_DATA; you're querying the underlying table, but the distinction isn't really important as they refer to the same combined object.

Based on the definition to added to the question later, it will refresh every day at midnight.


There are two schema objects. Physically the materialized view is a table. The materialized view objects contains the metadata about the refresh mode, type, method and other properties. If you check the ALL_OBJECTS you see that MATERIALIZED VIEW object doesn't have its segment. Data dictionary secures that you cannot treat the underlying table as normal table otherwise it can break the consistency between these coupled objects.

You also can create a materialized view on top of a prebuilt table.

UPDATE:

START WITH will update with every refresh. NEXT START is evaluated when view is created or refreshed.

Refresh can fail on any error that can normally occur in the database, e. g, not enough space, lock timeout, changes of the query underlying objects.