Question: What is the option to create a
"materialized view on prebuilt table"? When do I use the "on
prebuilt table" syntax?
Answer: The "materialized view on prebuilt
table" is used when you have already created a replicated table
using "create table as select" (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.
A complete refresh occurs when the Oracle materialized view is
initially defined, unless it references a prebuilt table, and a
complete refresh may be requested at any time during the life of the
Oracle materialized view.
The following is an example of an Oracle materialized view on
prebuilt table with an ON COMMIT refresh:
CREATE MATERIALIZED VIEW
empdep
ON PREBUILT TABLE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT empno, ename, dname, loc,
e.rowid emp_rowid,
d.rowid dep_rowid
FROM emp e, dept d
WHERE e.deptno = d.deptno;
Materialize View vs. Table
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 building 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.
This is where materialized view ON PREBUILT TABLE comes in very handy.
Taking a new approach via materialized view ON PREBUILT TABLE, first create the
table in the same name as the materialized view to be created:
SQL> create table mv1
(cnt number(10));
Next create the materialized view on prebuilt table by simply
adding the ON PREBUILT TABLE clause as shown below:
create materialized view
mv1
on prebuilt table
never refresh
as
select
cast(count (1) as number(10)) cnt
from t1;
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 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.
Sometimes with more complex code, you will run into an ORA-12058:
materialized view cannot use prebuilt table error. Follow the
link for more information on the
ORA-12058 error and the proper use of materialized view ON PREBUILT TABLE.
Follow the link for more detailed
information on materialized views, the ON PREBUILT TABLE command and
how to verify that this materialized view on prebuilt table process works as described.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
