Team Fly

Page 334

used if the users can't see them? In fact, because materialized views are so much like tables, you can give the users access to materialized views, but generally this is not done.

Instead, as indicated in Figure 9-8, the users always query the tables with the detail data—they don't usually query the materialized views directly. This is because the query optimizer in Oracle Database 10g knows about the materialized views and their relationships to the detail tables and can rewrite the query on the fly to access the materialized views instead. This results in huge performance gains without the user having to do anything special—just query the detail data. There is a maintenance benefit of this feature for the user as well: the queries do not have to change to point to different summary tables, as is the case with the more traditional summarization approach.

In order for the query to be rewritten, the structure of the materialized view must satisfy the criteria of the query. The following two listings demonstrate the query rewrite process. Let's assume we need to summarize the COMMISSION table we saw in the data compression section using the following query:

select prod_id, to_char(comm_date, 'YYYY-MM'), count(*), sum(comm_amt)
from commission
group by prod_id, to_char(comm_date, 'YYYY-MM');

Assume further that a materialized view (called comm_prod_mv) exists that contains summarized commission data by sales_rep_id, prod_id, and comm_date (full date). In this case, the query would be automatically rewritten as follows:

select prod_id, to_char(comm_date, 'YYYY-MM'), count(*), sum(comm_amt)
from comm_prod_mv
group by prod_id, to_char(comm_date, 'YYYY-MM');

By rewriting the query to use the materialized view instead, a large amount of data-crunching has been saved and the results will return much more quickly. Let's now turn our attention to determining what materialized views should be created.

When to Create Materialized Views

At this point, you may be asking yourself: ''How do I determine what materialized views to create and at what level of summarization?" Oracle Database 10g has some utilities to help. These utilities are collectively called the SQLAccess Advisor and will

Team Fly