Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page Go to next page
View PDF



Use the CREATE DIMENSION statement 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 of CREATE DIMENSION, you must run the DBMS_OLAP.validate_dimension procedure.

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.



Text description of statements_512.gif follows
Text description of create_dimension


Text description of statements_513.gif follows
Text description of level_clause


Text description of statements_514.gif follows
Text description of hierarchy_clause


Text description of statements_515.gif follows
Text description of join_clause


Text description of statements_516.gif follows
Text description of attribute_clause



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


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


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


Specify the name of the level

level_table . level_column

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

Restrictions on Level Columns


The hierarchy_clause 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.


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


Specify 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.


Specify the name of a level.


The join_clause lets you specify 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.


Specify 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 in the child of parent_level in the hierarchy_clause.


Specify the name of a level.

Restrictions on Join Dimensions


The attribute_clause lets you specify 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.


Creating a Dimension: Example

This statement was used to create the customers_dim dimension in the sample schema sh:

CREATE DIMENSION customers_dim 
   LEVEL customer   IS (customers.cust_id)
   LEVEL city       IS (customers.cust_city) 
   LEVEL state      IS (customers.cust_state_province) 
   LEVEL country    IS (countries.country_id) 
   LEVEL subregion  IS (countries.country_subregion) 
   LEVEL region     IS (countries.country_region) 
   HIERARCHY geog_rollup (
      customer      CHILD OF
      city          CHILD OF 
      state         CHILD OF 
      country       CHILD OF 
      subregion     CHILD OF 
   JOIN KEY (customers.country_id) REFERENCES country
   (cust_first_name, cust_last_name, cust_gender, 
    cust_marital_status, cust_year_of_birth, 
    cust_income_level, cust_credit_limit) 
   ATTRIBUTE country DETERMINES (countries.country_name)