Most of the time we come across very complex SQL or many tables to join and get our source data .
we are having option to get those all joins on the fly or Use Materialized Views
What is Materialized View
Materialized views (M View) are very much like a regular Oracle table, except that they are based on one or more tables. They can be simple or complex, read only, or updatable. Because an M view is a physical table, changes are managed by updating the effected rows in the M view when the underlying tables change. Updating an M view is called refreshing it.
There are two types of refresh: fast and full. In a full refresh, the M view is truncated and rebuild from the underlying tables. In a fast refresh, only the changes are updated in the M view. A forced refresh tells the View to try and execute a fast refresh, and if that fails, execute a full refresh. In order to execute a fast refresh, the underlying tables must have a materialized view log. This log records changes to the table so that the View can retrieve them.
Views are normally used to aggregate information in a data warehouse database. They can also be used in replicating data from one database to another. Unlike normal views, INSERT/UPDATE/DELETES of View require implementing advanced replication, which is far beyond the scope of this book. The View can be writable, which allows the data to be modified; however, the changes are not propagated back to the underlying tables and are lost when the View refreshes.
Syntax for creating M.View
How it is useful for Informatica
As all the joins are already done and data is ready for loading Source data fetch will be very fast .
we are having option to get those all joins on the fly or Use Materialized Views
What is Materialized View
Materialized views (M View) are very much like a regular Oracle table, except that they are based on one or more tables. They can be simple or complex, read only, or updatable. Because an M view is a physical table, changes are managed by updating the effected rows in the M view when the underlying tables change. Updating an M view is called refreshing it.
There are two types of refresh: fast and full. In a full refresh, the M view is truncated and rebuild from the underlying tables. In a fast refresh, only the changes are updated in the M view. A forced refresh tells the View to try and execute a fast refresh, and if that fails, execute a full refresh. In order to execute a fast refresh, the underlying tables must have a materialized view log. This log records changes to the table so that the View can retrieve them.
Views are normally used to aggregate information in a data warehouse database. They can also be used in replicating data from one database to another. Unlike normal views, INSERT/UPDATE/DELETES of View require implementing advanced replication, which is far beyond the scope of this book. The View can be writable, which allows the data to be modified; however, the changes are not propagated back to the underlying tables and are lost when the View refreshes.
Syntax for creating M.View
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
How it is useful for Informatica
As all the joins are already done and data is ready for loading Source data fetch will be very fast .