Oracle8i SQL Reference
Release 2 (8.1.6)






Prev Up Next

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.


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 DBMS_OLAP.VALIDATE_DIMENSION procedure. For information on this procedure, see Oracle8i Supplied PL/SQL Packages Reference

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.

Keywords and Parameters


is the schema in which the dimension will be created. If you do not specify schema, Oracle creates the dimension in your own schema. 


is the name of the dimension. The name must be unique within its schema. 


defines a level in the dimension. A level defines dimension hierarchies and attributes. 



is the name of the level 


level_table . level_column 

specifies from the columns in the level. You can specify up to 32 columns. The tables you specify in this clause must already exist.


  • All of the columns in a level must come from the same table.

  • If columns in different levels come from different tables, then you must specify the join_clause.

  • The set of columns you specify must be unique to this level.

  • The columns you specify cannot be specified in any other dimension.

  • Each level_column must be non-null. (However, these columns need not have NOT NULL constraints.)



defines a linear hierarchy of levels in the dimension. Each hierarchy forms a chain of parent-child relationships among the levels in the dimension. Hierarchies in a dimension are independent of each other. They may (but need not) have columns in common.

Each level in the dimension should be specified at most once in this clause, and each level must already have been named in the level_clause. 



is the name of the hierarchy. This name must be unique in the dimension. 



is the name of a level that has an n:1 relationship with a parent level: the level_columns of child_level cannot be null, and each child_level value uniquely determines the value of the next named parent_level.

If the child level_table is different from the parent level_table, you must specify a join relationship between them in the join_clause



is the name of a level. 


specifies an inner equijoin relationship for a dimension whose columns are contained in multiple tables. This clause is required and permitted only when the columns specified in the hierarchy are not all in the same table.


  • The child_key_columns must be non-null and the parent key must be unique and non-null. You need not define constraints to enforce these conditions, but queries may return incorrect results if these conditions are not true.

  • Each child key must join with a key in the parent_level table.

  • Self-joins are not permitted (that is, the child_key_columns cannot be in the same table as parent_level).




specifies one or more columns that are join-compatible with columns in the parent level.

If you do not specify the schema and table of each child_column, the schema and table are inferred from the CHILD OF relationship in the hierarchy_clause. If you do specify the schema and column of a child_key_column, the schema and table must match the schema and table of columns that comprise the child of parent_level in the hierarchy_clause




  • All of the child-key columns must come from the same table.

  • The number of child-key columns must match the number of columns in parent_level, and the columns must be joinable.

  • Do not specify multiple child key columns unless the parent level consists of multiple columns.

You can specify only one join_clause for a given pair of levels in the same hierarchy. 



is the name of a level. 


specifies the columns that are uniquely determined by a hierarchy level. The columns in level must all come from the same table as the dependent_columns. The dependent_columns need not have been specified in the level_clause.

For example, if the hierarchy levels are city, state, and country, then city might determine mayor, state might determine governor, and country might determine president


This statement creates a TIME dimension on table TIME_TAB, and creates a GEOG dimension on tables CITY, STATE, and COUNTRY.

   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
   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 ;

   LEVEL cityID         IS (, city.state)
   LEVEL stateID        IS state.state
   LEVEL countryID      IS
   HIERARCHY political_rollup (
      cityID            CHILD OF
      stateID           CHILD OF
         JOIN KEY city.state REFERENCES stateID
         JOIN KEY REFERENCES countryID);

Prev Up Next
Copyright © 1999 Oracle Corporation.

All Rights Reserved.