vb.net 2d barcode dll Materialized Views in Java

Making Data Matrix 2d barcode in Java Materialized Views

A brief introduction of materialized views makes sense in this chapter about views. The intent of this section is to illustrate the concept of materialized views, using a simple example. Normally, materialized views are mainly used in complex data warehousing environments, where the tables grow so big that the data volume causes unacceptable performance problems. An important property of data warehousing environments is that you don t change the data very often. Typically, there is a separate Extraction, Transformation, Loading (ETL) process that updates the data warehouse contents. Materialized views are also often used with distributed databases. In such environments, accessing data over the network can become a performance bottleneck. You can use materialized views to replicate data in a distributed database. To explore materialized views, let s revisit Listing 10-1 and add a third DDL command, as shown in Listing 10-21. Listing 10-21. Comparing Views, Tables, and Materialized Views SQL> create or replace VIEW dept20_v as 2 select * from employees where deptno = 20; View created. SQL> create TABLE dept20_t as 2 select * from employees where deptno = 20; Table created. SQL> create MATERIALIZED VIEW dept20_mv enable query rewrite as 2 select * from employees where deptno = 20; Materialized view created. SQL>

You already know the difference between a table and a view, but what is a materialized view Well, as the name suggests, it s a view for which you store both its definition and the query results. In other words, a materialized view has its own rows. Materialized views imply redundant data storage. The materialized view DEPT20_MV now contains all employees of department 20, and you can execute queries directly against DEPT20_MV, if you like. However, that s not the main purpose of creating materialized views, as you will learn from the remainder of this section.

Materialized views have two important properties, in the areas of maintenance and usage: Maintenance: Materialized views are snapshots. That is, they have a certain content at any point in time, based on refreshment from the underlying base tables. This implies that the contents of materialized views are not necessarily upto-date all the time, because the underlying base tables can change. Fortunately, the Oracle DBMS offers various features to automate the refreshment of your materialized views completely, in an efficient way. In other words, yes, you have redundancy, but you can easily set up appropriate redundancy control. Usage: The Oracle optimizer (the component of the Oracle DBMS deciding about execution plans for SQL commands) is aware of the existence of materialized views. The optimizer also knows whether materialized views are up-to-date or stale. The optimizer can use this knowledge to replace queries written against regular base tables with corresponding queries against materialized views, if the optimizer thinks that approach may result in better response times. This is referred to as the query rewrite feature, which is explained in the next section.

Note When you create materialized views, you normally specify whether you want to enable query rewrite, and how you want the Oracle DBMS to handle the refreshing of the materialized view. Those syntax details are used in Listing 10-21 but further usage information is omitted here. See Oracle SQL Reference for more information.

Let s continue with our simple materialized view, created in Listing 10-21. Assume you enter the following query, selecting all trainers from department 20: SQL> select * from employees where deptno = 20 and job = 'TRAINER' For this query, the optimizer may decide to execute the following query instead: SQL> select * from dept20_mv where job = 'TRAINER' In other words, the original query against the EMPLOYEES table is rewritten against the DEPT20_MV materialized view. Because the materialized view contains fewer rows than the EMPLOYEES table (and