Views & Materialized Views

Q1. Why are views created?

Answer. Views are created for:
• Storing complicated queries in the database so that they are not to be re-written every time they are required.
• Hide the complicated logics on joins from the users.
• Hide the sensitive data from the users by limiting their view to limited fields mentioned in views.
• Make querying the data easy for the users as complicated logics on joins are already embedded in views.

Q2. Difference between View and Materialized View.

Answer.

View	Materialized View
Data is up-to-date	Data is as of time when it was created or last refreshed
Data is not stored	Data is stored in the table
Indexes cannot be applied	Indexes can be applied
No need to refresh	It is refreshed automatically or manually
Only View query is stored in the Meta data tables & data is not stored	View Query along with Table used to store the data snapshot are stored in the meta data tables.
Slow in performance	Fast in performance

Q3. What is a force view?

Answer. Creates the view even if the underlying objects required for the view are not existing. It is used by import and other installation tools, to create a view before the underlying objects are created, to not have to figure out what objects are needed to be created in what order to succeed.

Q4. How are materialized views refreshed?

Answer. Materialized view can be refreshed Manually (On Demand) & Automatically (ON COMMIT, DBMS_JOB, DBMS_SCHEDULER).

Use one for the following command to refresh the materialized view manually.

DBMS_MVIEW.REFRESH ==> Refreshes one or more Oracle materialized views
DBMS_MVIEW.REFRESH_ALL_MVIEWS ==> Refreshes all Oracle materialized views
DBMS_MVIEW.REFRESH_DEPENDENT ==> Refreshes all table-based Oracle materialized views

Materialized View can be refreshed automatically by specifying ON COMMIT clause while creating materialized view. It can also be refreshed using scheduled jobs created using DBMS_JOBS or DBMS_SCHEDULER.

In case of any discrepancies or doubts do contact me.
Best of Luck Friends.