Shulou(Shulou.com)06/01 Report--

The advantage of creating a materialized view to add on prebuilt table is that under the schema of the created materialized view, there should be a table with the same name as the materialized view, and if the materialized view is updated, the table with the same name will also be updated. When you delete the materialized view, the table with the same name is not deleted and the updated data from the base table is retained. If you create a materialized view without on prebuilt table, you do not need to have a table with the same name as the materialized view under the schema of the materialized view you created.

When creating materialized views with on prebuilt table, there are a few points to pay attention to:

1. The schema of the materialized view you create must have a table with the same name as the materialized view.

two。 The table structure with the same name as the materialized view must be the same as the field name of the select query statement to create the materialized view, and correspond one to one.

1. Create the base table under test1:

SQL > conn test1/test1

Connected.

SQL > create table testdb1 (col1 varchar2 (20), col2 number)

Table created.

SQL > alter table testdb1 add constraint pk_a primary key (col1)

Table altered.

SQL > insert into testdb1 values ('1century 1)

1 row created.

SQL > insert into testdb1 values ('2mom 2)

1 row created.

SQL > commit

Commit complete.

two。 Create a materialized view log under test1.

SQL > create materialized view log on testdb1 with primary key

Materialized view log created.

SQL > select * from tab

TNAME TABTYPE CLUSTERID

MLOG$_TESTDB1 TABLE

RUPD$_TESTDB1 TABLE

TESTDB1 TABLE

3. Grant test2 relevant permissions under the SYS user:

SQL > conn / as sysdba

Connected.

SQL > grant select on test1.testdb1 to test2

Grant succeeded.

SQL > grant select on test1.MLOG$_TESTDB1 to test2

Grant succeeded.

SQL > grant create materialized view to test2

Grant succeeded.

4. Create materialized views under test2

SQL > conn test2/test2

Connected.

From the following error, you can see that on prebuilt table must create a table with the same name as the materialized view to create a materialized view.

SQL > create materialized view testdb2 on prebuilt table as select * from test1.testdb1

*

ERROR at line 1:

ORA-12059: prebuilt table "TEST2". "TESTDB2" does not exist

SQL > create table testdb2 (col1 varchar2 (20))

Table created.

From the following error, you can see that the created materialized view query data item must be the same as testdb2 and correspond one to one.

SQL > create materialized view testdb2 on prebuilt table as select * from test1.testdb1

Create materialized view testdb2 on prebuilt table as select * from test1.testdb1

*

ERROR at line 1:

ORA-12060: shape of prebuilt table does not match definition query

SQL > create materialized view testdb2 on prebuilt table as select col2 from test1.testdb1

Create materialized view testdb2 on prebuilt table as select col2 from test1.testdb1

*

ERROR at line 1:

ORA-12060: shape of prebuilt table does not match definition query

SQL > create materialized view testdb2 on prebuilt table as select col1 from test1.testdb1

Materialized view created.

SQL > select count (*) from testdb2

COUNT (*)

-

0

The testdb2 table has already made a materialized view:

SQL > delete from testdb2

Delete from testdb2

*

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

5. Update materialized view

SQL > exec dbms_mview.refresh ('testdb2','c')

PL/SQL procedure successfully completed.

SQL > select count (*) from testdb2

COUNT (*)

-

two

SQL > conn test1/test1

Connected.

SQL > insert into testdb1 values ('3')

1 row created.

SQL > commit

Commit complete.

SQL > conn test2/test2

Connected.

SQL > exec dbms_mview.refresh ('testdb2','c')

PL/SQL procedure successfully completed.

SQL > select count (*) from testdb2

COUNT (*)

-

three

6. Delete the materialized view under test2:

SQL > drop materialized view testdb2

Materialized view dropped.

It can be seen that a table with the same name still exists.

SQL > select count (*) from testdb2

COUNT (*)

-

three

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.