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

1. Create a materialized view

TEST8@ odb > create materialized view v_wuhua as select * from gaoshuiwei

Create materialized view vs. 001 asselect * from gaoshuiwei

*

ERROR at line 1:

ORA-01031: insufficient privileges

Sys@ odb > grant create materialized view to test8

TEST8@ odb > create materializedview v_wuhua as select * from gaoshuiwei

Materialized view created.

TEST8@ odb > create view v_shituas select * from gaoshuiwei

Create view v_shitu as select * from gaoshuiwei

*

ERROR at line 1:

ORA-01031: insufficient privileges

Sys@ odb > grant create view to test8

TEST8@ odb > create view v_shituas select * from gaoshuiwei

View created.

It is illegal to clear the v_wuhua data prompt data operation, which means that the materialized view can only be read and not deleted.

1. Compare the difference between materialized view and view

TEST8@odb > delete from gaoshuiwei; / / clear the base table

68310rows deleted.

Query the difference between materialized view and normal view

TEST8@odb > select count (*) from v_wuhua

COUNT (*)

-

68310

TEST8@odb > select count (*) from v_shitu

COUNT (*)

-

0

You can see that with the emptying of the base table, the materialized view does not change, while the normal view is empty; the effect of re-inserting the data is the same as the materialized view, and the normal view changes with the change of the base table.

TEST8@odb > insert into gaoshuiwei select * from all_objects

68314rows created.

TEST8@odb > commit

Commitcomplete.

TEST8@odb > select count (*) from gaoshuiwei

COUNT (*)

-

68314

TEST8@odb > select count (*) from v_wuhua

COUNT (*)

-

68310

TEST8@odb > select count (*) from v_shitu

COUNT (*)

-

68314

TEST8@odb > exec dbms_mview.refresh ('VroomWUHUA'); / / Refresh the materialized view data is now consistent with the base table data.

Materialized view can refresh data not only completely but also incrementally.

TEST8@odb > create materialized view v_wuhua1 as select * from gaoshuiwei whererownumselect count (*) from v_wuhua1

COUNT (*)

-

0

Selectdbms_metadata.get_ddl ('MATERIALIZED_VIEW','V_WUHUA1','TEST8') FROM DUAL

3. Query rewriting

TEST8@odb > create materialized view v_wuhua3 enable query rewrite as select * fromgaoshuiwei

Materializedview created.

Take a look at the query execution plan of the base table. On the execution plan, the superficial query is that the base table is actually querying materialized views.

4. Delete the materialized view

DROPMATERIALIZED VIEW V_TESTMV

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.