So far, we've talked about the original tables stored in physical form in databases. In which we have access to all the attributes of the tables. What happens if we have to restrict a user to access some attributes of the table and allow access to the other attributes. Like, an employee in an administrative department can look up the name, address, designation, age and other factors of an employee table. But he must not be authorized to view or access any employee's salary.
In these cases, we must be able to create a virtual table that can display only the attributes required by a table. This is possible through View and Materialized View which we will talk about in this article. We will also discuss the differences between the view and the materialized view with the help of the comparison table shown below:
|Basic||A view is never stored, it is only displayed.||A materialized view stored on the disk.|
|To define||Displays the virtual table made up of one or more base tables or views.||The view materialized a physical copy of the base table.|
|To update||The view is updated every time the virtual table is used (View).||The materialized view must be updated manually or using triggers.|
|speed||Slow processing.||Fast processing|
|Memory usage||The view does not require memory space.||The materialized view uses memory space.|
|Syntax||Create V view as||Create V materialized view (clause) Update (clause) On (Trigger) As|
Definition of sight
The view one virtual table, created using the command Create view . This virtual table contains the data recovered from a query expression, in the Create View command. The view can be created from one or more tables or base views. You can query a view as if you were querying the original base tables.
Not that the view is precalculated is saved on the disk, however, a Vista calculated every time it is used or accessed. Whenever a view is used, the query expression in the Create View command is executed at that particular time. So, you always get the data updated in one view.
If you update any content in View, you are reflected in the original table, and if changes have been made to the original base table, it would be reflected in its view. But this Slows the performance of a view. For example, a view is created by joining two or more tables. In that case, you have to pay time to resolve Joins every time a view is used.
But it has some advantages such as not It requires storage space . can create a view personalized of a complex database. possible to prevent the user to access confidential information in a database. It reduces the complexity queries by retrieving data from multiple tables in a single custom view.
Now let's see the syntax of View
Create V view as
Remember that all views are not updatable. Like a view created using the clause DISTINCT, the clause Group By, the bond CHECK (if control constraints violate), the option of read only cannot be updated.
Definition of materialized view
The materialized view physical copy of the original base tables. The view materialized as snapshot or an image of the original base tables. Like View, it also contains data retrieved from the expression of queries of the command Create materialized visualization .
But unlike View, the materialized View precalculated is saved on a disk as an object and does not come updated every time it is used. Instead, the materialized view needs to be updated manually or with the help of triggers . The process of updating the materialized view is called materialized view maintenance .
The materialized View responds faster than View. because the materialized view precalculated and, therefore, does not waste time in solving the query or joins in the query that creates the materialized view. Which in turn responds faster to the question asked about a materialized view.
Let us check the syntax of the materialized view:
Create a materialized view V Build (clause) Update (type) ON (trigger) Like
Where the clause decides Build, when to compile the materialized View. The update type decides how to update the materialized view and the trigger decides when to update the materialized view.
The materialized displays are generally used in the data warehouse .
Key differences between sight and materialized sight
- The fundamental difference between View and materialized View that Views not I'm archived physically on the disk. On the other hand, materialized Views are stored on the disk.
- The view can be defined as one virtual table created as a result of the query expression. However, the Vista materialized a physical copy, an image or snapshot of the base table.
- A view always comes updated as the query that creates View runs every time the view is used. On the other hand, Materialized View is updated manually or by applying gods triggers now.
- The materialized View responds faster of View while the materialized View precalculated.
- The materialized view uses the memory space while stored on disk, while Vista only a display therefore it does not require memory space.
The materialized View responds faster than View. But View always provides updated information to the user.