The “ON PREBUILT TABLE” clause is used when you have already created a replicated table using “CTAS” and now want that table to accept query rewrite. In this case, you can convert an existing table to a materialized view by using the “ON PREBUILT TABLE” clause.
• At the segment level, the materialized view and the table are the same. The biggest difference between a table and a materialized view is the inability to add or modify columns in a materialized view while it is quite simple to do with a table.
• The standard approach to build a materialized view creates both a table and a materialized view; however, the data object id for the materialized view will be NULL. In this situation, dropping the materialized view automatically drops the table because it did not have an independent existence.
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
82
SQL> CREATE TABLE my_objects AS SELECT object_name,object_type,created FROM user_objects;
Table created.
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
83
SQL> SELECT COUNT(*) FROM my_objects;
COUNT(*)
----------
82
SQL> CREATE MATERIALIZED VIEW my_objects
2 ON PREBUILT TABLE
3 AS
4 SELECT object_name,object_type,created FROM user_objects;
Materialized view created.
SQL> SELECT build_mode,last_refresh_type,refresh_method,refresh_mode,compile_state,staleness,rewrite_enabled FROM user_mviews WHERE mview_name = ' MY_OBJECTS';
BUILD_MODE LAST_REFRESH_TYPE REFRESH_METHOD REFRESH_MODE COMPILE_STATE STALENESS REWRITE_ENABLED
----------- ------------------ --------------- ------------- -------------- ---------- -------------
PREBUILT NA FORCE DEMAND VALID UNKNOWN N
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
84
SQL> SELECT COUNT(*) FROM my_objects;
COUNT(*)
----------
82
SQL> CREATE TABLE another_table
2 (
3 id NUMBER
4 );
Table created.
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
85
SQL> SELECT COUNT(*) FROM my_objects;
COUNT(*)
----------
82
SQL> SELECT object_name,data_object_id,object_type FROM all_objects WHERE object_name IN ('MY_OBJECTS','ANOTHER_TABLE','USER_OBJECTS');
OBJECT_NAME DATA_OBJECT_ID OBJECT_TYPE
------------ ----------------- ---------------
USER_OBJECTS VIEW
USER_OBJECTS SYNONYM
ANOTHER_TABLE 78081 TABLE
MY_OBJECTS 78079 TABLE
MY_OBJECTS MATERIALIZED VIEW
--Once the MV is built, any DML attempt on my_objects returns an ORA-01732 error.
SQL> INSERT INTO my_objects VALUES ('A_DUMMY','TABLE',SYSDATE);
INSERT INTO my_objects VALUES ('A_DUMMY','TABLE',SYSDATE)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
--Force Refresh.
SQL> EXEC dbms_mview.refresh('MY_OBJECTS');
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
85
SQL> SELECT COUNT(*) FROM my_objects;
COUNT(*)
----------
85
--The MV definition has to be the same as the underlying table, else I get an ORA-12060 error.
--However, if I drop the MV, I can query the underlying table (still called "MY_OBJECTS") and execute DML on it.
SQL> DROP TABLE another_table;
Table dropped.
SQL> DROP TABLE my_objects;
DROP TABLE my_objects
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "COURSE"."MY_OBJECTS"
SQL> DROP MATERIALIZED VIEW my_objects;
Materialized view dropped.
SQL> SELECT object_name,data_object_id,object_type FROM all_objects WHERE object_name IN ('MY_OBJECTS','ANOTHER_TABLE','USER_OBJECTS');
OBJECT_NAME DATA_OBJECT_ID OBJECT_TYPE
------------ ----------------- ---------------
USER_OBJECTS VIEW
USER_OBJECTS SYNONYM
MY_OBJECTS 78079 TABLE
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
83
SQL> SELECT COUNT(*) FROM my_objects;
COUNT(*)
----------
85
SQL> INSERT INTO my_objects VALUES ('A_DUMMY','TABLE',SYSDATE);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT COUNT(*) FROM user_objects;
COUNT(*)
----------
83
SQL> SELECT COUNT(*) FROM my_objects;
COUNT(*)
----------
86
--The Table MY_OBJECTS still exists and shows the data as was present when it was refreshed as an MV, with the additional row from an INSERT as well.
--However, I can no longer refresh it as a Materialized View.
SQL> EXEC dbms_mview.refresh('MY_OBJECTS');
BEGIN dbms_mview.refresh('MY_OBJECTS'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3012
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 216
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 196
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2844
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3255
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3287
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1
--Thus, an MV can be built on an underlying Table. Once the MV is built, manipulation is by way of MV Refresh and not directly on the Table. If the MV is dropped, it is only the MV definition that "goes away", the Table persists even with data and DML activity can be performed.As a result of the materialized view on prebuilt table clause, there are now two objects just as before – one table and one materialized view. The materialized view took over the command over the segment; however, the table already existed, so the table object was not recreated during the process.
One clever thing about the materialized view on prebuilt table process is that it makes it impossible to drop the prebuilt table without first dropping the materialized view. Even if the materialized view is dropped, the table will revert to its former status as an independent segment.
Of course, the table could then be deleted, if desired, but better yet, it is now possible to do anything allowed with a table, such as select from it, create indexes and modify or add columns.
Once the table has been modified to suit you, just use ON PREBUILT TABLE to recreate the materialized view and you’re back in business in no time with your materialized view on prebuilt table. The end user sees no difference except that the materialized view ON PREBUILT TABLE eliminates the burden of the “standard” drop and recreate of a materialized view and associated table.
A materialized view ON PREBUILT TABLE can be used on tables of all sizes; however, you’re likely to see the most benefit from its use on larger tables where the time impact of a regular drop and rebuild of the materialized view could be on the order of magnitude of hours or days.
