Oracle's physical visual management

Oracle
Oracle's physical visual management

Author: ccbzzp


1. The concept of physical visualization
The materialized view management is used for the total, the prediction, recovery or distribution of the image, in a large number of library use it can improve the sum,count,avg,min,max of the table, such as the speed of inquiry, as long as the physical image management to create a system, The search optimizer will automatically use the physical visual management, which is called Query REWRITE. Unlike a normal view, the actual view manages the storage data, which occupies the actual space of the data library (s).
The rights of the created Visual management user must be: Create materialzed view,create table,create View,select, if created in other modes, create any on the table Materialized view and select permissions.
To find out more about the materialized view management in the REWRITE model, you also need to have the global QUERY OR QUERY REWRITE permissions for the referenced table.
If you are planning to use physical visual management, modify the parameters of the reference file add: Query_rewrite_enable=true

2. Create a visual note:
After you create it, do you want to fill in the data
How long to refresh
Use that type of refresh: Comple (complete), fast (FAST), FORCE (compelling), NEVER (never)

3. Create a physical view of the map
CREATE materialized VIEW TEST3
PCTFREE 0 tablespace Mtest
STORAGE (INITIAL 1M NEXT 1M pctincrease 0)
Build DEFERRED
REFRESH FAST on COMMIT
ENABLE QUERY REWRITE
As SELECT Emp_no,sum (QTY_PSC) as QTY_PSC from Bsempms GROUP by Emp_no;

4. Delete physical images
DROP materialized VIEW TEST3;

5. Tools for managing and evaluating physical images
To run Dbmssnap.sql and dbmssum.sql to actually create a package of Dbms_mview and Dbms_olap.
Dbms_mview is used to perform management activities.
DBMS_OLAP determines whether the physical view can improve the performance of the search.

6. The way to refresh the physical visual images
EXECUTE Dbms_mview. REFRESH (' TEST3 ', ' C ');
Where TEST3 is the physical image name, ' C ' is one of the refreshing types.
Refresh types are: C-Full refresh
F-Quick refresh and compelling refresh
EXECUTE Dbms_mview. Refresh_all
Do not accept any parameters, refresh all.

7. Visualizing the integrity of the images
The question of the completeness of the data that is caused by the different ways in which the images are refreshed and how different the time is when the physical view is nested, in this case, it is recommended to put the associated physical image in the same refresh group, and to adjust the refresh.
The Dbms_refresh software package provides the make process to create a refresh group.
For example:
EXECUTE Dbms_refresh. Make
(name=> ' Test_group ',
List=> ' LOC_EMP,LOC_DPT ',
Next_date=>sysdate,
Interval=> ' sysdate+7 ');
The above creates a physical visual refresh group Test_group, with a refresh cycle of 7 days at a time.
Two physical images loc_emp and LOC_DPT pass through one of the parameters to the process.

8. To add a refreshed physical view to the refresh group
Dbms_refresh. ADD
(NAME in VARCHAR2,
LIST in varchar2,|
TAB in Dbms_utility. Unclarray,
LAX in Boolean:=false);

9. Erase the refreshed visual images into the refresh group
Dbms_refresh. Subtract
(NAME in VARCHAR2,
LIST in varchar2,|
TAB in Dbms_utility. Unclarray,
LAX in Boolean:=false);

10. Manually delete a refresh group
EXECUTE Dbms_refresh. REFRESH (' Test_group ');
can also
EXECUTE Dbms_refresh. DESTROY (name=> ' test_group ');

11. Materialized Video Log Management
The physical view log is a table that maintains a historical record of the physical visual operation.
To create a materialized video log, you have to create an after row trigger on a table, and you must have create Trigger,create table permissions

For example:
CREATE materialized VIEW LOG on Bsempms
Tablespace data_test
STORAGE (INITIAL 1M NEXT 1M pctincrease 0)
PCTFREE 5 pctused 90;
The pctfree of the physical video log should be small and pctused should be large.
The alter materialized VIEW log command allows you to modify the physical visual log parameters.
Such as:
ALTER materialized VIEW LOG Bsempms PCTFREE 1;
To withdraw the video log, you can use the drop materialized VIEW log command
Such as:
DROP materialized VIEW LOG on Bsempms;

12. Purge the physical video log
To reduce the amount of space used in the physical video log, you can use the Purge_log process in the Dbms_mview software package, Purge_log has three parameters:
Main Table name, num change, delete marker
Num refers to the number of physical images that have recently been refreshed at least
For example:
EXECUTE Dbms_mview. Purge_log
(mater=> ' Bsempms ',
Num=>1,
Flag=> ' DELETE ');
The physical view of the Bsempms table clears the most recently used terms for the materialized view.
To intercept the primary table without losing the physical visual log, you can use the command
TRUNCATE TABLE Bsempms PRESERVE materialized VIEW LOG;

13. You can also use OEM to create and manage physical images