Can i replace an existing normal table with a materialized view

Question

I've searched but found no answer, so i resort to you.

I have an Oracle table, say "Countries" that exists for ages in database 2 (target). Now, we want to synchronize that table from a "Countries" table in database 1 (source). However the existing Countries table (target), has pk, constraints, triggers, etc and is referenced by several other tables.

Question: Could i replace the target table with a Materialized view (MV) ? If so, i would have to recreate all those pk, constraints, triggers, on this MV, and also alter all those tables that reference the original table, right ?

Thank you in advance

Answer 1

You can create a materialized view using an existing table with the ON PREBUILT TABLE clause. It will create the automation, etc. for the MV and update the original table. See documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-MATERIALIZED-VIEW.html#GUID-EE262CA4-01E5-4618-B659-6165D993CA1B

ON PREBUILT TABLE Clause

The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. This clause is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view.

If the materialized view is dropped, then the preexisting table reverts to its identity as a table.

Note:This clause assumes that the table object reflects the materialization of a subquery. Oracle strongly recommends that you ensure that this assumption is true in order to ensure that the materialized view correctly reflects the data in its master tables. The ON PREBUILT TABLE clause could be useful in the following scenarios:

You have a table representing the result of a query. Creating the table was an expensive operation that possibly took a long time. You want to create a materialized view on the query. You can use the ON PREBUILT TABLE clause to avoid the expense of executing the query and populating the container for the materialized view.

You temporarily discontinue having a materialized view, but keep its container table, using the DROP MATERIALIZED VIEW ... PRESERVE TABLE statement. You then decide to recreate the materialized view and you know that the master tables of the view have not changed. You can create the materialized view using the ON PREBUILT TABLE clause. This avoids the expense and time of creating and populating the container table for the materialized view.

If you specify ON PREBUILT TABLE, then Oracle database does not create the I_SNAP$ index. This index improves fast refresh performance. If you want the benefits of this index, then you can create it manually. Refer to Oracle Database Data Warehousing Guide for more information.