Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 6 of 20
To create a dimension. A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (or "level") can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite. The Summary Advisor uses these relationships to recommend creation of specific materialized views.
Note:
Oracle does not automatically validate the relationships you declare when creating a dimension. To validate the relationships specified in the hierarchy_clause and the join_clause, you must run the |
See Also:
|
To create a dimension in your own schema, you must have the CREATE
DIMENSION
system privilege. To create a dimension in another user's schema, you must have the CREATE
ANY
DIMENSION
system privilege. In either case, you must have the SELECT
object privilege on any objects referenced in the dimension.
This statement creates a TIME
dimension on table TIME_TAB
, and creates a GEOG
dimension on tables CITY
, STATE
, and COUNTRY
.
CREATE DIMENSION time LEVEL curDate IS time_tab.curDate LEVEL month IS time_tab.month LEVEL qtr IS time_tab.qtr LEVEL year IS time_tab.year LEVEL fiscal_week IS time_tab.fiscal_week LEVEL fiscal_qtr IS time_tab.fiscal_qtr LEVEL fiscal_year IS time_tab.fiscal_year HIERARCHY month_rollup ( curDate CHILD OF month CHILD OF qtr CHILD OF year) HIERARCHY fiscal_year_rollup ( curDate CHILD OF fiscal_week CHILD OF fiscal_qtr CHILD OF fiscal_year ) ATTRIBUTE curDate DETERMINES (holiday, dayOfWeek) ATTRIBUTE month DETERMINES (yr_ago_month, qtr_ago_month) ATTRIBUTE fiscal_qtr DETERMINES yr_ago_qtr ATTRIBUTE year DETERMINES yr_ago ; CREATE DIMENSION geog LEVEL cityID IS (city.city, city.state) LEVEL stateID IS state.state LEVEL countryID IS country.country HIERARCHY political_rollup ( cityID CHILD OF stateID CHILD OF countryID JOIN KEY city.state REFERENCES stateID JOIN KEY state.country REFERENCES countryID);
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|