CODEWITHSUNDEEP
sqloracle-databaseviewmaterialized-views
Dreamer
Dreamer

Reputation: 7549

Why direct reference materialized view is not recommended in Oracle?

Quoted from Oracle online document

Unlike indexes, materialized views can be accessed directly using a SELECT statement. However, it is recommended that you try to avoid writing SQL statements that directly reference the materialized view, because then it is difficult to change them without affecting the application. Instead, let query rewrite transparently rewrite your query to use the materialized view.

I am not quite understand what exact meaning of direct reference materialized view in the context, it sounds like SELECT statement is OK but it is not recommended to join materialized view with order table/views by foreign key or even the select is not recommended at all? Why?

Upvotes: 2

Views: 2424

Answers (1)

Justin Cave
Justin Cave

Reputation: 231681

The documentation is suggesting that where you are using materialized views to pre-aggregate or pre-compute results, it is better to let query rewrite work rather than directly referencing the materialized view in a query. If you are using materialized views to replicate data from a remote database, then query rewrite wouldn't come in to play and you would have to reference the materialized view directly.

Assuming that you are using materialized views to pre-aggregate results, let's work through a quick example. Say that you have a simple data warehouse where you want to get the total sales for every product for the month. Using the tables directly, you might have something like

SELECT p.product_name, 
       trunc( s.sales_date, 'MM' ) sales_month, 
       sum( s.sales_amt ) total_sales
  FROM sales_fact s
       JOIN product_dim p
         ON( s.product_id = p.product_id )
 GROUP BY p.product_name, trunc( s.sales_date, 'MM' )

If you want to optimize that, there are multiple materialized views that you could create and then rely on query rewrite to use those materialized views rather than hitting the tables directly. For example, you might create a materialized view that aggregates the sales for each product for each day

CREATE OR REPLACE MATERIALIZED VIEW sales_by_product_by_day
  ...
  ENABLE QUERY REWRITE
AS
    SELECT p.product_name, 
           trunc( s.sales_date ) sales_day, 
           sum( s.sales_amt ) total_sales
      FROM sales_fact s
           JOIN product_dim p
             ON( s.product_id = p.product_id )
     GROUP BY p.product_name, trunc( s.sales_date )

You could, of course, then go back and rewrite your query to use the sales_by_product_by_day materialized view, i.e.

SELECT product_name,
       trunc( sales_day, 'MM' ) sales_month,
       sum( total_sales ) total_sales
  FROM sales_by_product_by_day
 GROUP BY product_name, trunc( sales_day, 'MM' )

It would generally be better, however, to leave the query as it was (referencing the sales_fact and product_dim views) and to allow query rewrite to optimize the query by using the sales_by_product_by_day materialized view. From a performance perspective, it doesn't matter whether you reference the materialized view directly or whether Oracle uses query rewrite to use the materialized view when you query the base tables.

By writing queries against base tables, however, you make it much easier for developers and DBAs in the future to tweak the set of materialized views that are available to optimize the full set of queries that people are running without having to constantly rewrite the queries. For example, it might turn out that you wanted your materialized view to aggregate by product by store by month. That materialized view would be a bit less efficient for this particular query but perhaps that would be more efficient for a number of other queries. You could, of course, maintain both materialized views if you wanted to (incurring the overhead of maintaining many materialized views). But, just as with indexes, you're often better off with a smaller set of indexes that work reasonably well for most queries rather than a large number of indexes that are ideal for each query.

In most data warehouse environments, the set of queries that people run will evolve over time so the set of materialized views that you want to use will also evolve over time. If your queries go against the base tables, you can add and modify materialized views and use query rewrite to let the optimizer pick those up and to choose the best materialized view to rewrite against. If your queries reference a particular materialized view, however, then Oracle can't rewrite against a different materialized view when one becomes available and changing the set of available materialized views in the future means manually rewriting a lot of queries.

Upvotes: 5

Related Questions