This information will cowl find out how to:
Configuring for Materialized Views
To begin with the configuration for materialized views it is advisable to login to the Oracle database as a system administrator. To try this, use the “SQLPLUS” command or the “SQL developer” instrument.
Tips on how to Grant Create Materialized View Privilege?
To grant the create materialized view privilege to any particular consumer, the “GRANT” command with the “CREATE” assertion can be utilized. The instance is given beneath:
GRANT CREATE MATERIALIZED VIEW TO C##MD;
Within the above instance, “C##MD” is the consumer.
Output
The output confirmed {that a} privilege has been granted to the consumer.
Tips on how to Create a Materialized View?
In Oracle, the “CREATE” assertion may be utilized to create a materialized view.
Instance 1: Making a Materialized View
Let’s see an instance of making a easy materialized view:
AS SELECT NAME, ADDRESS, SUM(CREDIT_LIMIT)
FROM CUSTOMERS
GROUP BY NAME, ADDRESS;
Within the above instance, “linuxhint_mv_s” is the materialized view of the “CUSTOMERS” desk. Numerous columns together with the NAME, ADDRESS, and CREDIT_LIMIT together with the SUM() perform are specified within the choose record.
Output
The output depicts that the materialized view of the CUSTOMERS desk has been efficiently created.
Instance 2: Making a Materialized View With a Particular Situation
Let’s see one other instance of making a materialized view, with a selected situation:
AS SELECT NAME, ADDRESS, COUNT(DISTINCT CREDIT_LIMIT)
FROM CUSTOMERS
WHERE CUSTOMER_ID > 20
GROUP BY NAME, ADDRESS;
Within the above instance, solely these rows shall be included within the materialized view the place the worth of CUSTOMER_ID is larger than 20.
Output
The output confirmed that the materialized has been created underneath a particular situation.
Tips on how to Refresh a Materialized View?
To refresh a materialized view “DBMS_MVIEW.REFRESH” can be utilized which is a PL/SQL process within the Oracle database. The command to refresh a materialized view is given beneath:
EXECUTE DBMS_MVIEW.REFRESH(‘linuxhint_mv’);
Within the above command, “linuxhint_mv” is the materialized view.
Output
The output confirmed the materialized view has been refreshed.
If the consumer needs to carry out a quick refresh for a materialized view, then go the “F” flag because the argument to the “DBMS_MVIEW.REFRESH”. This can refresh the materialized view primarily based on the modifications which have occurred within the base tables for the reason that final refresh, which makes it quicker than a whole refresh. The instance is given beneath:
EXECUTE DBMS_MVIEW.REFRESH(‘linuxhint_mv_s’, ‘F’);
Within the above instance, “linuxhint_mv_s” is the materialized view.
Output
The output portrays the materialized view has been efficiently refreshed.
Tips on how to Set the Measurement of a Materialized View?
To set the dimensions for a materialized view, the “ALTER” command can be utilized with the “STORAGE” clause. This clause permits you to specify the quantity of disk house that the materialized view ought to use. The instance of setting the utmost dimension for a materialized view is given beneath:
STORAGE (MAXSIZE 100M);
Within the above instance, the “linuxhint_mv_s” is a materialized view.
Output
The output depicts that the materialized view has been altered.
Tips on how to Monitor the Refresh Time for a Materialized View?
To observe the refresh time for a materialized view merely use the “SELECT” assertion to show the data of “user_mviews”. The command is given beneath:
FROM user_mviews
WHERE mview_name IN (
SELECT identify
FROM all_dependencies
WHERE referenced_owner = consumer AND referenced_name=”CUSTOMERS”
);
Output
The output confirmed the refresh time for a materialized view.
Tips on how to Rebuild a Materialized View?
To rebuild a materialized view, you may carry out a whole refresh by utilizing the “C” flag within the DBMS_MVIEW.REFRESH, as given beneath:
EXECUTE DBMS_MVIEW.REFRESH(‘linuxhint_mv’, ‘C’);
Output
The output depicts that the materialized view has been refreshed.
Conclusion
The configuration of materialized view contains granting the create materialized view privilege, making a materialized view, refreshing a materialized view, setting the dimensions for a materialized view, monitoring the refresh time for a materialized view, and rebuilding a materialized view. This information defined the steps to configure for materialized views.
