Re: materialized views

> Are they the answer to
>the 'which aggregate table to select from?' problem?
>

In theory, yes.
Creating materialized views is VERY similar to creating snapshots. You can then define what the relationship is between the key columns in the summary and the key columns in the base table (e.g. months contain weeks contain days).

Oracle will then read a query against the base table, examine the list of associated materialized views, the columns used in the query, and the dimension descriptions and decide which materialized view is the best table to use for the query, and rewrite the query accordingly.

e.g.

        select year, month, sum(sales) from daily_sales; become

        select year, month, sales from monthly_sales;

There are sundry little refinements, like whether you create the summary table and tell Oracle to leave it alone, or whether Oracle (re)creates the table or keeps it up to date using a log file. You can also tell Oracle that the summary view is not necessarily accurate to the second, but should be used anyway, etc.

I haven't yet implemented in a big way, so I don't know how well the system behaves under pressure, but I certainly expect to get rid of some of the clunky middle layers of code that I have to use in v7.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk Received on Fri Jun 04 1999 - 11:03:43 CDT