Oracle Database 10g – Materialized Views

Oracle Database 10g - Materialized Views
Em , ,

Materialized views were first introduced in Oracle8i and they are part of a component known as Summary Management. Your organization may already be using a materialized view, but may know it by another name, such as a summary or aggregate table. Here we will discuss how materialized views are created and managed and how the query rewrite capability will transparently rewrite your SQL query to use a materialized view to improve query response time. This allows users of the database to be completely unaware of which materialized views exist.

The materialized view should be thought of as a special kind of view, which physically exists inside the database, it can contain joins and or aggregates and exists to improve query execution time by pre-calculating expensive joins and aggregation operations prior to execution.

Today, organizations using their own summaries waste a significant amount of time manually creating summaries, identifying which ones to create, indexing the summaries, updating them and advising their users on which ones to use.

Now the DBA will only have to initially create the materialized view, it can then be automatically updated whenever changes occur to its data source. There is also a SQL Access Advisor component which will recommend to the DBA which materialized views to create, delete and retain.

One of the biggest benefits of using materialized views will be seen by the users of the data warehouse or database. No longer will they have to be told by the DBA which materialized views exist. Instead, they can write their query against the tables or views in the database. Then the query re-write mechanism in the Oracle server will automatically re-write the SQL query to use the materialized views. Thus resulting in a significant improvement in query response time and eliminating the need for the end-user to be ‘summary aware’.

Versão 1.0
Tamanho 243 KB
Criado 1 de dezembro de 2014
Atualizado 24 de março de 2015