Re: Use for CREATE DIMENSION in O10g?

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Thu, 25 Mar 2004 22:25:20 GMT
Message-ID: <k%I8c.124589$Wa.112630@news-server.bigpond.net.au>

"John Haskins" <donoteventrytospamme_at_thisaddress.com> wrote in message news:yfidnWi0uLJLzf7dRVn-hA_at_adelphia.com...
> I've read about the CREATE DIMENSION command in Oracle 10g, but I'm not
> really seeing a purpose for it. What does it give that isn't already
> available through traditional relational design?
>
> Thanks in advance.
>
>
>

John,

One suggestion would be to read Ch.13 "Materialized Views" of "Expert One-on-One Oracle" by Thomas Kyte (A-Press:2003) to see where and when DIMENSIONs should be used.

My reading of that text is that a DIMENSION tells the CBO when it should consider using a MATERIALIZED VIEW for columns that are not in the query but can be derived from other columns. A dimension is another way to inform the CBO about the relationship between columns in that it is a hierarchy of containers.

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.

Douglas Hawthorne Received on Thu Mar 25 2004 - 16:25:20 CST