Tables and Materialised views in Oracle

Table:

In Oracle, or any other relational database for that matter, a table is the heart of the database.

Table is the combination of rows and columns. The data that is entered into a database gets stored in the tables that are mentioned in the DML Queries.

To retrieve data from one or more tables we issue the select statement or query with relevant filters and sorts as per our requirement.

The query can vary from a simple select * from table to complex one joining copious tables.

Operations that can be performed on a table are listed below:

• Create
• Query or Select
• Update
• Delete
• Insert
• Truncate
• Drop
• Export / Import
• Partition
• Compression
• Alter

Before we jump on to materialized views, we need to look at normal views as it is an important concept to understand materialized views (MV) better!!

Views:

Views are nothing but the logical name of a result set of select query.

To be more precise, View can be termed as a synonym or an alias for a select query. View will not be occupying any space in the database, since it is just a logical interpretation.

Example : User is issuing a query to retrieve the records

select a.val1 , b.val2, c.val3,a.val2 from a,b,c where a.val2=b.val1 and b.val2=c.val1;

instead of issuing this query everytime, if we create a view as

create view abc as select a.val1 , b.val2, c.val3,a.val2 from a,b,c where a.val2=b.val1 and b.val2=c.val1;

We can query as select * from abc;

Which is quite simple.

Materialized Views:

Now coming to the next term , Materialized view. MV is nothing but the combination of both Tables and views.

Every MV is associated with a backend table and a select query. Unlike, normal views , MVs occupy space in the database as it involves backend table.

Now imagine, When we create a view and querying it, from coding perspective it simplifies the number of lines, enables re-usability and readability. whereas on the performance perspective, it does all the resource intensive scans, indexing, sorting, etc,. each and every time we try to access it.

When it comes to MV, it stores the result set in a backend table, thus improves the performance. The next question that arises is how is the result set gets updated when the MV stores the records in a separate table and access from that table.

To address this issue, MV has the concept of Refresh. we can schedule how often the MV table to be refreshed to update the result set from the underlying tables.

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command.

1. Force: First tries to refresh with fast mechanism if possible or else will use a complete refresh. This is default refresh type.
2. Never: Suppresses all refreshes on materialized views.
3. Fast: Apply the changes made since the last refresh.

• Fast refresh using materialized view logs
• Fast refresh using ROWID range: Can do the fast refresh after the direct load, based on the rowed of the new rows.

Materialized Views restrictions

• Underlying Query cannot contain non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
• The query cannot contain any references to RAW or LONG RAW datatypes or object REFs.
• For PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions.
• MV with FAST refresh cannot contain any subquery

MV an Example:

The end users can either access the tables via a complex select query or can use the simple MV.

Materialized views improve the performance drastically when implemented correctly.

Hope this helps!