Re: Use for CREATE DIMENSION in O10g?

"John Haskins" <donoteventrytospamme_at_thisaddress.com> wrote in message news:X6adnWiSBIYH5v7dRVn-sw_at_adelphia.com...
> Douglas,
> Thanks for the response.
> This sounds interesting in theory, but it seems like from a practical
> standpoint, as soon as the user wants a summary by year_and_quarter they
> would simply query a different set of columns or materialized views (or
> their reporting tool would on their behalf). Put another way, this feature
> seems to do something that was already available, and perhaps do it in a
way
> that leads to sloppy design (much like untyped variables in a programming
> language).
>
> But I could be missing something obvious. Feel free to punch me in the arm
> if I am.
>
>
>
>
> > For example, a dimension would Oracle that a year contains many
> > year_and_quarter columns which, in turn, contain many many months, etc.
> If
> > I have a materialized year summarized by month and I want a summary by
> > year_and_quarter, then, with the dimension, the CBO would consider using
> the
> > materialized view as the basis for the summary, instead of using the
base
> > tables. Otherwise, the CBO would not consider the materialized view
> because
> > it has no way of knowing the relationship between the year_and_quarter
and
> > month columns.
>
>

John,

I think there may be a misunderstanding about what a materialized view can do for you.

If I have a query that gets the total sales by product by region by month, then I can create a materialized view which stores the result of that query. The next time I do the same query, the CBO will consider using those results from the materialized view instead of doing the same work over again. (There are a few points about data currency and query rewriting which I will gloss over at this stage). Now if I query total sales by product by region by quarter, the CBO will not consider the materialized view even though I know that months can be rolled up into quarters. There is where a dimension comes in - a dimension tells the CBO that months be rolled up into quarters. Now the CBO can consider using the materialized view by summarizing the months into quarters.

In your approach, there would be two or more materialized views to be queried upon. This means extra storage is needed for the extra matrialized views. Received on Thu Mar 25 2004 - 19:04:22 CST